数据格式如下:
dot(int) logdate(datetime)
45 2011-7-7 2:45:00
185 2011-7-7 7:34:00
123 2011-7-7 11:20:00
51 2011-7-7 11:32:00
10 2011-7-7 12:02:00
...如果我想查询某天的时段数据合计,难道必须写24个select吗?
我最终是想得到一个1行24列的数据集,如上,第11列即为11点至12点的合计174,
我想用case 语句,但是行不通
dot(int) logdate(datetime)
45 2011-7-7 2:45:00
185 2011-7-7 7:34:00
123 2011-7-7 11:20:00
51 2011-7-7 11:32:00
10 2011-7-7 12:02:00
...如果我想查询某天的时段数据合计,难道必须写24个select吗?
我最终是想得到一个1行24列的数据集,如上,第11列即为11点至12点的合计174,
我想用case 语句,但是行不通
sum(case when convert(varchar(2),logdate,108)='00' then dot else 0 end) as [00],
sum(case when convert(varchar(2),logdate,108)='01' then dot else 0 end) as [01],
sum(case when convert(varchar(2),logdate,108)='02' then dot else 0 end) as [02],
sum(case when convert(varchar(2),logdate,108)='03' then dot else 0 end) as [03],
......sum(case when convert(varchar(2),logdate,108)='23' then dot else 0 end) as [23]
from tb group by convert(varchar(10),logdate,120)
select convert(varchar(13),logdate,120) dt,sum(dot)
from tb group by convert(varchar(13),logdate,120)