同样编码015可以出现两次?
select 编码,销量
,case
WHEN ROW_NUMBER() OVER (order by 销量 desc)/2360 <0.05 then 'A'
WHEN ROW_NUMBER() OVER (order by 销量 desc)/2360 between 0.05 and 0.25 then 'B'
ELSE 'C'
end as ABC
from tb
select 编码,销量
,case
WHEN ROW_NUMBER() OVER (order by 销量 desc)/2360 <0.05 then 'A'
WHEN ROW_NUMBER() OVER (order by 销量 desc)/2360 between 0.05 and 0.25 then 'B'
ELSE 'C'
end as ABC
from tb
DECLARE @t TABLE(id INT,Qty INT,ABC NVARCHAR(10))
INSERT INTO @t
SELECT 1,200,N'A' UNION ALL
SELECT 1,300,N'B' UNION ALL
SELECT 1,120,N'C' UNION ALL
SELECT 2,200,N'A' UNION ALL
SELECT 2,200,N'B' UNION ALL
SELECT 3,200,N'A' UNION ALL
SELECT 3,100,N'B' UNION ALL
SELECT 3,300,N'C' SELECT a.id,a.Qty,ABC=CAST(a.Qty*100/b.SumQty as VARCHAR)+'%'
FROM @t a LEFT JOIN (SELECT id,SUM(Qty)as SumQty FROM @t GROUP BY id) b
ON a.id=b.id
(8 row(s) affected)
id Qty ABC
----------- ----------- -------------------------------
1 200 32%
1 300 48%
1 120 19%
2 200 50%
2 200 50%
3 200 33%
3 100 16%
3 300 50%--试下看看..