怎样按时间段分组？

[majorsoft]

表结构
id(char) consume_time(datetime)
00000002 2003-08-26 11:46:00.000
00001047 2003-08-22 16:37:00.000
00001047 2003-08-26 16:34:00.000
00001074 2003-08-14 10:02:00.000
00001074 2003-08-14 10:03:00.000
比如下面的是按时间点分组
select id,consume_time,count(*)
from dbo.m_consume
group by id,consume_time
having count(*)>1

我现在要按每天一时间段（比如把2003-08-22日 7:00:00.000到 10:00:00.000 分为一组，而2003-08-23日 7:00:00.000到 10:00:00.000也分为一组 ），

[liuyun2003]

不明白。

[CrazyFor]

select id,case when right(convert(char(17),consume_time,120),5)=>'07:00' and right(convert(char(17),consume_time,120),5)<='10:00' then '07:00-10:00' else '11:00-07:00' end,count(*)
from dbo.m_consume
group by id,case when right(convert(char(17),consume_time,120),5)=>'07:00' and right(convert(char(17),consume_time,120),5)<='10:00' then '07:00-10:00' else '11:00-07:00' end
having count(*)>1

[majorsoft]

to CrazyFor(蚂蚁)
你这样是按时间段分组，但把所有的日期的时间段分在一起了。
请注意"比如把2003-08-22日 7:00:00.000到 10:00:00.000 分为一组，而2003-08-23日 7:00:00.000到 10:00:00.000也分为一组 "
要把不同日期的时间段分开。。。

[愉快的登山者]

按日期分组：
select id, convert(char(10), consume_time, 120), count(*)
from table1 group by id, convert(char(10), consume_time, 120)

按日期时间段分组：
select id, convert(char(10), consume_time, 120), count(*)
from table1
where datepart(hh, consume_time) >= 7 and datepart(hh, consume_time) <= 10
group by id, convert(char(10), consume_time, 120)

[txlicenhe]

Select id,CONVERT(varchar(10),consume_time,120),count(*)
from m_consume
where convert(char(8),consume_time,108) between '07:00:00' and '10:00:00'
group by id,CONVERT(varchar(10),consume_time,120)

[愉快的登山者]

按日期分组：
select id, convert(char(10), consume_time, 120), count(*)
from table1 group by id, convert(char(10), consume_time, 120)

按日期时间段分组：
select id, convert(char(10), consume_time, 120), count(*)
from table1
where datepart(hh, consume_time) >= 7 and datepart(hh, consume_time) <= 10
group by id, convert(char(10), consume_time, 120)

[dafu71]

Select id,CONVERT(varchar(10),consume_time,120),count(*)
from m_consume
where datepart(hh,consume_time) between 7 and 10 group by id,
CONVERT(varchar(10),consume_time,120)