34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT micapsdm.yyyymmdd as [日期]
,micapsdm.sid
,[08时]=max(case when MicapsDm.times='08' then MicapsDm.cms else null end)
,[11时]=max(case when MicapsDm.times='11' then MicapsDm.cms else null end)
,[14时]=max(case when MicapsDm.times='14' then MicapsDm.cms else null end)
,[17时]=max(case when MicapsDm.times='17' then MicapsDm.cms else null end)
,[20时]=max(case when MicapsDm.times='20' then MicapsDm.cms else null end)
,[23时]=max(case when MicapsDm.times='23' then MicapsDm.cms else null end)
,[02时]=max(case when MicapsDm.times='02' then MicapsDm.cms else null end)
,[05时]=max(case when MicapsDm.times='05' then MicapsDm.cms else null end)
,[r1]=max(case when MicapsDm.times='02' then cast(MicapsDm.Rr as int) else 0 end)
,[r2]=max(case when MicapsDm.times='08' then cast(MicapsDm.Rr as int) else 0 end)
,[r3]=max(case when MicapsDm.times='14' then cast(MicapsDm.Rr as int) else 0 end)
,[r4]=max(case when MicapsDm.times='20' then cast(MicapsDm.Rr as int) else 0 end)
FROM MicapsDm
group by micapsdm.yyyymmdd,micapsdm.sid
order by micapsdm.sid,micapsdm.yyyymmdd
with t
as(
SELECT micapsdm.yyyymmdd as [日期]
,micapsdm.sid
,[08时]=max(case when MicapsDm.times='08' then MicapsDm.cms else null end)
,[11时]=max(case when MicapsDm.times='11' then MicapsDm.cms else null end)
,[14时]=max(case when MicapsDm.times='14' then MicapsDm.cms else null end)
,[17时]=max(case when MicapsDm.times='17' then MicapsDm.cms else null end)
,[20时]=max(case when MicapsDm.times='20' then MicapsDm.cms else null end)
,[23时]=max(case when MicapsDm.times='23' then MicapsDm.cms else null end)
,[02时]=max(case when MicapsDm.times='02' then MicapsDm.cms else null end)
,[05时]=max(case when MicapsDm.times='05' then MicapsDm.cms else null end)
,[r1]=max(case when MicapsDm.times='02' then cast(MicapsDm.Rr as int) else 0 end)
,[r2]=max(case when MicapsDm.times='08' then cast(MicapsDm.Rr as int) else 0 end)
,[r3]=max(case when MicapsDm.times='14' then cast(MicapsDm.Rr as int) else 0 end)
,[r4]=max(case when MicapsDm.times='20' then cast(MicapsDm.Rr as int) else 0 end)
FROM MicapsDm
group by micapsdm.yyyymmdd,micapsdm.sid
order by micapsdm.sid,micapsdm.yyyymmdd
)
select [日期],sid,[08时],......,[05时],([r1]+[r2]+[r3]+[r4]) as rtotal from t
r1]=max(case when MicapsDm.times='02' then cast(MicapsDm.Rr as int) else 0 end)
+max(case when MicapsDm.times='08' then cast(MicapsDm.Rr as int) else 0 end)
+max(case when MicapsDm.times='14' then cast(MicapsDm.Rr as int) else 0 end)
+max(case when MicapsDm.times='20' then cast(MicapsDm.Rr as int) else 0 end)
r=max(case when MicapsDm.times in ('02' ,'08','14','20') then cast(MicapsDm.Rr as int) else 0 end)
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([xrq] datetime,[rgl] int,[jcmc] varchar(12))
insert [tbl]
select '2012-04-01',4182,'51路' union all
select '2012-04-01',598,'机2(7)' union all
select '2012-04-01',2520,'711路' union all
select '2012-04-02',258,'宝山社区罗罗' union all
select '2012-04-02',5092,'淞安专线' union all
select '2012-04-02',504,'1211路' union all
select '2012-04-02',778,'302路' union all
select '2012-04-03',2515,'彭罗专线' union all
select '2012-04-03',3852,'51路' union all
select '2012-04-03',148,'机2(7)' union all
select '2012-04-03',1930,'711路' union all
select '2012-04-03',50,'特约二'
declare @str varchar(max)
set @str=''
select @str=@str+',['+replace(convert(varchar(10),[xrq],120),'-','')+']'+
'=sum(case when [xrq]='+quotename(replace(convert(varchar(10),[xrq],120),'-','')
,'''')+' then [rgl] else 0 end)'
from tbl group by replace(convert(varchar(10),[xrq],120),'-',''),[xrq]
exec('select isnull([jcmc],''竖向求和'') as xrq'+@str
+',sum(rgl) as [横向和] from [tbl] group by [jcmc] with rollup')
/*
xrq 20120401 20120402 20120403 横向和
1211路 0 504 0 504
302路 0 778 0 778
51路 4182 0 3852 8034
711路 2520 0 1930 4450
宝山社区罗罗 0 258 0 258
机2(7) 598 0 148 746
彭罗专线 0 0 2515 2515
淞安专线 0 5092 0 5092
特约二 0 0 50 50
竖向求和 7300 6632 8495 22427
*/
--用这个方法试试