表中有createtime,num两字段
如何取每一天的各整点之间的各NUM数之和?
假设我想取出每天8点到24点之间,各时间段内的各NUM之和。即想得到如下格式的数: 【比如2007-08-01】
8 ***
9 ***
10 ***
11 ***
12 ***
。如何写SQL,谢谢!
如何取每一天的各整点之间的各NUM数之和?
假设我想取出每天8点到24点之间,各时间段内的各NUM之和。即想得到如下格式的数: 【比如2007-08-01】
8 ***
9 ***
10 ***
11 ***
12 ***
。如何写SQL,谢谢!
from tb group by convert(varchar(13)
select sum(num)
from tb
where datepart(hh,createtime) between 8 and 24
group by convert(varchar(8),createtime,112)
from tb
group by convert(varchar(13),createtime,120)
select datepart(hh,createtime) [hour],sum(num) num
from tb
where datepart(hh,createtime) between 8 and 24
group by convert(varchar(13),createtime,120)
from tb
where convert(varchar(10),createtime,120)='2007-08-01' and datepart(hh,creattime) between 8 and 23
group by convert(varchar(13),createtime,120)
时间 NUM
8:01 100
8:02 101
8:03 102就想得到 8点的这个平均值 (100+101+102)/3
同样得到9点的平均值、10点的平均值.............
from tb
where convert(varchar(10),createtime,120)='2007-08-01' and datepart(hh,creattime) between 8 and 23
group by datepart(hh,creattime)
select convert(varchar(13),createtime,120),avg(num)
from tb group by convert(varchar(13)
用AVG()这样解决的。谢谢