求教一个SQL的写法，百度知道最多只能悬赏100分，不够我还能发问题追加，只求解答。

昨晚看过题目，今天开了一天的年会~~~
说说我的做法
新建一个表
CREATE TABLE [dbo].[copy_t](
[n] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[n] ASC
)
)
插入数据是1到1W，目的是用来分割固定格式的字符串成表形式，速度上不错，以前一个老外DBA来公司培训的时候交的

1.用表变量
declare @table table (
[票据ID] [varchar](100) NOT NULL,
[商品ID] [varchar](100) NULL,
[价格] [money] NULL,
[客户ID] [varchar](100) NULL
)

insert into @table
SELECT a.[票据ID]
, a.[商品ID]
,convert(money,SUBSTRING(a.[价格], n,CHARINDEX(',', a.[价格] + ',', n) - n)) as [价格]
,[客户ID]
FROM [票据表] a
JOIN a3s.dbo.copy_t
ON n <= LEN(a.[价格]) AND SUBSTRING(',' + a.[价格], n, 1) = ','
WHERE CHARINDEX(',',a.[价格]) > 0
union all
SELECT 票据ID, 商品ID, convert(money,[价格]) as [价格], 客户ID
FROM [票据表] b
WHERE CHARINDEX(',',b.[价格]) <= 0 and b.[价格] <> '未定'

SELECT
a.[商品ID]
,MAX(a.[价格]) as [最高成交价格]
,(select top 1 [客户名称] from @table as b,[客户表] as c WHERE [价格] = MAX(a.[价格]) and b.客户ID = c.客户ID) as [max客户名称]
,min(a.[价格]) as [最低成交价格]
,(select top 1 [客户名称] from @table as b,[客户表] as c WHERE [价格] = min(a.[价格]) and b.客户ID = c.客户ID) as [min客户名称]
,AVG(a.[价格]) as [平均价格]
FROM @table as a
WHERE 1=1
group by a.[商品ID]

2.用SQL Server 2005以上的CTE方式
with cte as (
SELECT a.[票据ID]
, a.[商品ID]
,convert(money,SUBSTRING(a.[价格], n,CHARINDEX(',', a.[价格] + ',', n) - n)) as [价格]
,[客户ID]
FROM [票据表] a
JOIN a3s.dbo.copy_t
ON n <= LEN(a.[价格]) AND SUBSTRING(',' + a.[价格], n, 1) = ','
WHERE CHARINDEX(',',a.[价格]) > 0
union all
SELECT 票据ID, 商品ID, convert(money,[价格]) as [价格], 客户ID
FROM [票据表] b
WHERE CHARINDEX(',',b.[价格]) <= 0 and b.[价格] <> '未定'
)
SELECT
a.[商品ID]
,MAX(a.[价格]) as [最高成交价格]
,(select top 1 [客户名称] from cte as b,[客户表] as c WHERE [价格] = MAX(a.[价格]) and b.客户ID = c.客户ID) as [max客户名称]
,min(a.[价格]) as [最低成交价格]
,(select top 1 [客户名称] from cte as b,[客户表] as c WHERE [价格] = min(a.[价格]) and b.客户ID = c.客户ID) as [min客户名称]
,AVG(a.[价格]) as [平均价格]
FROM cte as a
WHERE 1=1
group by a.[商品ID]

其实都差不多，只是2005以上不用创建表变量罢了，表变量和LZ说的临时表效果是一样的

因为“也可能是多个用逗号分开的数字字符，如'12,14.5'，”， 所以本题无解，除非使用存储过程或者使用程序代码单独处理。
建议将 含有“'12,14.5'”的记录拆成两条或者多条记录。 将价格的字段类型改成number，问题会简单的多。“未定”可以用null表示。

还是在程序里来判断吧。。。。
有逗号的就分割一下，正常价格和汉字就好办了

你没试吗？这就是所有占总销量80%的所有客户，您糊涂了吗？即然您只要了80%的客户，都是一个百分数，还有什么多少？还排什么序？
不过，看在分的面子上，后面我又给了您占总销量80%以上的所有客户按销量反排序的查询。

MSSQL:
select
A,
SUM(B) AS 销售额合计,
100*SUM(B)/(SELECT SUM(B) FROM 表) AS 占总百分比
FROM 表 group by A
HAVING 100*SUM(B)/(SELECT SUM(B) FROM 表)=80

select
A,
SUM(B) AS 销售额合计,
100*SUM(B)/(SELECT SUM(B) FROM 表) AS 占总百分比
FROM 表 group by A
HAVING 100*SUM(B)/(SELECT SUM(B) FROM 表)=80

占总销量80%以上的所有客户按销量排序。

select
A,
SUM(B) AS 销售额合计,
100*SUM(B)/(SELECT SUM(B) FROM 表) AS 占总百分比
FROM 表 group by A
HAVING 100*SUM(B)/(SELECT SUM(B) FROM 表)>=80
order by SUM(B) desc

问得好。