现有表
id lx value
1 a 10
2 b 20
3 b 11
4 a 15
5 a 16
生成查询
如果lx为a则表示一班,b表示2班,值是所有lx为a的和
一班 二版
(10+15+16) (20+11)
id lx value
1 a 10
2 b 20
3 b 11
4 a 15
5 a 16
生成查询
如果lx为a则表示一班,b表示2班,值是所有lx为a的和
一班 二版
(10+15+16) (20+11)
一班=sum(case when lx='a' then value else 0 end ),
二班=sum(case when lx='b' then value else 0 end )
from [Table] group by lx
select
一班=sum(case lx when 'a' then value else 0 end),
二版=sum(case lx when 'b' then value else 0 end)
from table_name
group by table_name--> 纵向
select lx, sum(value) from table_name group by lx
declare @tb table ([id] int,[lx] nvarchar(1),[value] nvarchar(10))
Insert into @tb
select 1,'a',10 union all
select 2,'b',20 union all
select 3,'b',11 union all
select 4,'a',15 union all
select 5,'a',16
--Select * from @tb
SELECT [lx],
MAX( CASE seq WHEN 1 THEN [value] ELSE '0' END ) + '+' +
MAX( CASE seq WHEN 2 THEN [value] ELSE '0' END ) + '+' +
--MAX( CASE seq WHEN 3 THEN [value] ELSE '' END ) + '+ ' +
MAX( CASE seq WHEN 3 THEN [value] ELSE '0' END ) as expression
FROM ( SELECT p1.[lx], p1.[value],
( SELECT COUNT(*)
FROM @tb p2
WHERE p2.[lx] = p1.[lx]
AND p2.[value] <= p1.[value] )
FROM @tb p1 ) D ( [lx], [value], seq )
GROUP BY [lx] ;
/*
lx expression
---- --------------------------------
a 10+15+16
b 11+20+0(2 row(s) affected)
*/
二班 = SUM(CASE WHEN lx = 'b' THEN val ELSE 0 END)
FROM test
最终是这么用的,只要一行
是不是这样效率比较慢呀