求Sql 按 时间段来 集计数据 ？？

[舞台中央的我]

id createtime xiaoshoushu
----------- ------------------------------------------------------ -----------
1 2012-03-09 01:01:00.000 1
2 2012-03-09 01:02:00.000 2
3 2012-03-09 02:15:00.000 3
4 2012-03-09 03:01:00.000 5
5 2012-03-09 03:15:00.000 1
6 2012-03-09 05:15:00.000 3
7 2012-03-09 05:35:00.000 4
8 2012-03-09 05:45:00.000 3

假如 我表里 数据 是这样的
我想 取得 时间带别的 xiaoshoushu 的 集计
就是 Createtime = 2012/03/09 的 零点到 一点 ， 一点到两点，两点到三点，三点到四点，四点到五点，五点到六点，六点到七点，。。。。。。。。。。。。。。。 一直到 23点 到 24点 的 分别的 销售数

望 大侠给 解答

[AcHerat]

select convert(varchar(10),createtime,120) date,datepart(hh,createtime) hh,sum(xiaoshoushu) xiaoshu
from tb
where convert(varchar(10),createtime,120) = '2012-03-09'
group by convert(varchar(10),createtime,120),datepart(hh,createtime)

[AcHerat]

如果中间有缺省的也要统计进去，楼主可以自己设计一个对应时间段的临时表去left join主表，继而进行相应的统计。

[-晴天]

create table tb(id int,createtime datetime,xiaoshoushu int)
insert into tb select 1,'2012-03-09 01:01:00.000',1
insert into tb select 2,'2012-03-09 01:02:00.000',2
insert into tb select 3,'2012-03-09 02:15:00.000',3
insert into tb select 4,'2012-03-09 03:01:00.000',5
insert into tb select 5,'2012-03-09 03:15:00.000',1
insert into tb select 6,'2012-03-09 05:15:00.000',3
insert into tb select 7,'2012-03-09 05:35:00.000',4
insert into tb select 8,'2012-03-09 05:45:00.000',3
go
select convert(varchar(13),createtime,120),sum(xiaoshoushu)
from tb
group by convert(varchar(13),createtime,120)
/*
              
------------- -----------
2012-03-09 01 3
2012-03-09 02 3
2012-03-09 03 6
2012-03-09 05 10

(4 行受影响)

*/
go
drop table tb

[舞台中央的我]

谢谢 一楼二楼 三楼

[开着拖拉机泡妞]

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[id] int,
[createtime] datetime,
[xiaoshoushu] int
)
go
insert [tbl]
select 1,'2012-03-09 01:01:00.000',1 union all
select 2,'2012-03-09 01:02:00.000',2 union all
select 3,'2012-03-09 02:15:00.000',3 union all
select 4,'2012-03-09 03:01:00.000',5 union all
select 5,'2012-03-09 03:15:00.000',1 union all
select 6,'2012-03-09 05:15:00.000',3 union all
select 7,'2012-03-09 05:35:00.000',4 union all
select 8,'2012-03-09 05:45:00.000',3 union all
select 1,'2012-03-09 01:01:00.000',1 union all
select 2,'2012-03-09 01:02:00.000',2 union all
select 3,'2012-03-09 18:15:00.000',3 union all
select 4,'2012-03-09 03:01:00.000',5 union all
select 5,'2012-03-09 03:15:00.000',1 union all
select 6,'2012-03-09 05:15:00.000',3 union all
select 7,'2012-03-09 19:35:00.000',4 union all
select 8,'2012-03-09 12:45:00.000',3 union all
select 1,'2012-03-09 07:01:00.000',1 union all
select 2,'2012-03-09 06:02:00.000',2 union all
select 3,'2012-03-09 02:15:00.000',3 union all
select 4,'2012-03-09 11:01:00.000',5 union all
select 5,'2012-03-09 23:15:00.000',1 union all
select 6,'2012-03-09 22:15:00.000',3 union all
select 7,'2012-03-09 1:35:00.000',4 union all
select 8,'2012-03-09 00:45:00.000',3

create table #tbl(
[id] int,
[createtime] datetime,
[xiaoshoushu] int,
[hour] int
)
select * from #tbl
insert #tbl
select *,case when DATEPART(MM,[createtime])=0 then 
DATEPART(HH,[createtime]) else DATEPART(HH,[createtime])+1 
end as [hour] from tbl

declare @hour int
declare @str varchar(max)
set @str=''
select @str=@str+','+'['+CAST([hour]-1 as varchar)+'点到'+CAST([hour] as varchar)+'点]'+
'=sum(case when [hour]='+cast([hour] as varchar)+' then [xiaoshoushu] else 0 end)'
from #tbl group by [hour]
set @str='select convert(varchar(10),[createtime],120) as [createtime]'
+@str+' from #tbl group by convert(varchar(10),[createtime],120)'
print @str
exec(@str)
/*
createtime	0点到1点	1点到2点	2点到3点	3点到4点	5点到6点	6点到7点	7点到8点	11点到12点	12点到13点	18点到19点	19点到20点	22点到23点	23点到24点
2012-03-09	3	10	6	12	13	2	1	5	3	3	4	3	1
*/

[舞台中央的我]

谢谢了 不过 结贴了