求SQL语句

[ballatong]

现有表T1 如下
Item Qty TotalCost
QQ 100 400.00
FF 20 120.00
HH 18 280.00
F2 300 800.00
F3 28 350.00
H3 46 370.00
G8 55 640.00
G9 30 580.00
G3 15 700.00
G2 10 240.00
H11 78 200.00
E5 30 560.00
E7 76 880.00
E9 20 678.00
EQ 11 460.00
F4 200 500.00
F5 20 300.00
W1 37 660.00
W2 15 768.00
W3 19 270.00
W4 20 180.00



要求是按照Totalcost 排降序列出Itecode,Qty字段，另外再加上一个类别字段 C ， 内容如下，如果这个Itemcode的 Totalcost 在排序下来的总的Totalcost的前60%里面 ，类别就设置成字符 ‘A’如果这个Itemcode的 Totalcost 在排序下来的总的Totalcost的61%到90%，类别就设置成字符 ‘B'

请问怎么写？ 谢谢大神

[不穿内裤的超人]

我不确定我是否读懂题目了,你看看是否你想要的结果

;with cte(Item,Qty,TotalCost) as
(select 'QQ','100','400.00' union 
select 'FF','20','120.00' union 
select 'HH','18','280.00' union 
select 'F2','300','800.00' union 
select 'F3','28','350.00' union 
select 'H3','46','370.00' union 
select 'G8','55','640.00' union 
select 'G9','30','580.00' union 
select 'G3','15','700.00' union 
select 'G2','10','240.00' union
select 'E5','30','560.00' union
select 'E7','76','880.00' union
select 'E9','20','678.00' union
select 'EQ','11','460.00' )
select * into #tmp from cte --第一步



select *,cn=COUNT(*)over(),rw=ROW_NUMBER()over(order by TotalCost) into #all from #tmp --得到cn和rw列，方便计算列的百分比进度

select Item,Qty,TotalCost,C=case when bl<60 then 'A' when bl between 61 and 90 then 'B' else 'C' end
--这里百分比判断自己定义，我只是按照题目给了方法，很简单
,rw,cn,bl from ( --rw是排序顺序数,cn是总行数 ,方法就是算比率
select *,bl=cast(rw as decimal(10,2))/cast(cn as decimal(10,2)) from #all--得到字段所在的百分比
)a --最后给出结果

drop table #tmp
drop table #all

[不穿内裤的超人]

看错了,降序..重新写了，比率也没*100就直接算了

;with cte(Item,Qty,TotalCost) as
(select 'QQ','100','400.00' union 
select 'FF','20','120.00' union 
select 'HH','18','280.00' union 
select 'F2','300','800.00' union 
select 'F3','28','350.00' union 
select 'H3','46','370.00' union 
select 'G8','55','640.00' union 
select 'G9','30','580.00' union 
select 'G3','15','700.00' union 
select 'G2','10','240.00' union
select 'E5','30','560.00' union
select 'E7','76','880.00' union
select 'E9','20','678.00' union
select 'EQ','11','460.00' )
select * into #tmp from cte --第一步



select *,cn=COUNT(*)over(),rw=ROW_NUMBER()over(order by TotalCost desc) into #all from #tmp --得到cn和rw列，方便计算列的百分比进度

select Item,Qty,TotalCost,C=case when bl<60 then 'A' when bl between 61 and 90 then 'B' else 'C' end
--这里百分比判断自己定义，我只是按照题目给了方法，很简单
,rw,cn,bl from ( --rw是排序顺序数,cn是总行数 ,方法就是算比率
select *,bl=cast(rw as decimal(10,2))/cast(cn as decimal(10,2))*100 from #all--得到字段所在的百分比
)a --最后给出结果
 

Item	Qty	TotalCost	C	rw	cn	bl
E7	76	880.00	A	1	14	7.1428571428500
F2	300	800.00	A	2	14	14.2857142857100
G3	15	700.00	A	3	14	21.4285714285700
E9	20	678.00	A	4	14	28.5714285714200
G8	55	640.00	A	5	14	35.7142857142800
G9	30	580.00	A	6	14	42.8571428571400
E5	30	560.00	A	7	14	50.0000000000000
EQ	11	460.00	A	8	14	57.1428571428500
QQ	100	400.00	B	9	14	64.2857142857100
H3	46	370.00	B	10	14	71.4285714285700
F3	28	350.00	B	11	14	78.5714285714200
HH	18	280.00	B	12	14	85.7142857142800
G2	10	240.00	C	13	14	92.8571428571400
FF	20	120.00	C	14	14	100.0000000000000