表A
ID ADD_TIME SL
1 2011-09-22 11:23:52 2
2 2011-09-24 11:36:52 4
3 2011-09-24 14:21:52 2
4 2011-09-24 15:25:52 3
5 2011-09-24 15:06:52 3
6 2011-09-24 19:36:52 1
7 2011-09-24 20:36:52 7
8 2011-09-24 22:36:52 11
9 2011-09-26 20:36:52 12如上,如何取得2011-09-24号,每个小时时段的SL总和?得到如下11 4
14 2
15 6
19 1
20 7
22 11
ID ADD_TIME SL
1 2011-09-22 11:23:52 2
2 2011-09-24 11:36:52 4
3 2011-09-24 14:21:52 2
4 2011-09-24 15:25:52 3
5 2011-09-24 15:06:52 3
6 2011-09-24 19:36:52 1
7 2011-09-24 20:36:52 7
8 2011-09-24 22:36:52 11
9 2011-09-26 20:36:52 12如上,如何取得2011-09-24号,每个小时时段的SL总和?得到如下11 4
14 2
15 6
19 1
20 7
22 11
select datepart(hh,add_time) as hh,sum(sl) as sl
from a
where convert(varchar(8),add_time,112) = '20110924'
group by datepart(hh,add_time)
select datepart(hh,add_time) as hh,sum(sl) as sl
from a
where add_time like '2011-09-24%'
group by datepart(hh,add_time)
from a
where convert(varchar(8),add_time,112) = '20110924'
group by datepart(hh,add_time)
order by datepart(hh,add_time)
-- 楼上都可以,不过都用上了列运算,在大数据量下,那就惨了,我稍微改下
select datepart(hh,add_time) as hh,sum(sl) as sl
from a
where add_time >= '2011-09-24' and add_time < '2011-09-25'
group by datepart(hh,add_time)
order by datepart(hh,add_time)
datepart(hh,add_time) as hh,sum(sl) as sl
from
a
where
convert(varchar(8),add_time,120) = '2011-09-24'
group by
datepart(hh,add_time)