求SQL

ballatong 2018-09-14 03:11:59

现有表T1 如下
Item Qty Cost
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

要求1.按照Totalcost 排降序列出Itecode,Qty字段，
2.增加加上一个类别字段 C ，内容如下，如果这一行Itemcode的 Cost 在排序下来的Cost的总和（sum(cost)）前60%里面，类别就设置成字符 ‘A’如果这个Itemcode的 cost 在排序下来的总的cost的61%到90%，类别就设置成字符 ‘B'

请问怎么写？谢谢大神

...全文

233 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

ballatong 2018-09-17

打赏
举报

谢谢楼上完美！

砸死牛顿的苹果 2018-09-15

打赏
举报

if OBJECT_ID('tempdb.dbo.#t') is not null drop table #t

go

create table #t(Item varchar(10),Qty int,Cost decimal(10,2))

insert #t

select 'QQ','100','400.00'

 union all select 'FF','20','120.00'

 union all select 'HH','18','280.00'

 union all select 'F2','300','800.00'

 union all select 'F3','28','350.00'

 union all select 'H3','46','370.00'

 union all select 'G8','55','640.00'

 union all select 'G9','30','580.00'

 union all select 'G3','15','700.00'

 union all select 'G2','10','240.00'

 union all select 'H11','78','200.00'

 union all select 'E5','30','560.00'

 union all select 'E7','76','880.00'

 union all select 'E9','20','678.00'

 union all select 'EQ','11','460.00'

 union all select 'F4','200','500.00'

 union all select 'F5','20','300.00'

 union all select 'W1','37','660.00'

 union all select 'W2','15','768.00'

 union all select 'W3','19','270.00'

 union all select 'W4','20','180.00'

go

alter table #t add c varchar(1)

go

update t set c=case when c1<=c2*0.6 then 'A' ELSE 'B' end  

	from (select top 100 percent *,(select sum(cost) from #t b where b.Cost >=a.Cost)c1,sum(cost)over()c2 from #t a order by cost desc)t

go

select * from #t order by cost desc

/*

Item	Qty	Cost	c

E7	76	880.00	A

F2	300	800.00	A

W2	15	768.00	A

G3	15	700.00	A

E9	20	678.00	A

W1	37	660.00	A

G8	55	640.00	A

G9	30	580.00	A

E5	30	560.00	B

F4	200	500.00	B

EQ	11	460.00	B

QQ	100	400.00	B

H3	46	370.00	B

F3	28	350.00	B

F5	20	300.00	B

HH	18	280.00	B

W3	19	270.00	B

G2	10	240.00	B

H11	78	200.00	B

W4	20	180.00	B

FF	20	120.00	B

*/