SQL分类汇总问题

[neverlove7]

我的SQL数据库本来是按日期分类查询的，近斯要分时间段查询。
（其中表里有RQ，TIME字段，我是用RQ+TIME来合并的。但WHERE不会写了。）
如果我将时间段设置成下午14:00,就是说每次查询的时候查每天14:00到次日14:00里的内容
应该用什么方法来实现呢？（目的就是查询每月的以每天14：00为分割点的数据）请高手赐教！

[neverlove7]

很急啊，没有人回（会）吗？—#9825；

[neverlove7]

谢谢，只不过查询的是一个月的数量♡

[dawugui]

查每天14:00到次日14:00里的内容?

set @dt1 as datetime
set @dt1 = '2008-05-26'

select * from tb where convert(varchar(15),日期字段,120) >= convert(varchar(10),@dt1,120) + ' 14:00' and convert(varchar(15),日期字段,120) < convert(varchar(10),@dt1+1,120) + ' 14:00'

[nzperfect]

group by rq变成成字符串 + case when time 大于14点为A else B end
这个思路吧

[nzperfect]

我错了,还要考虑跨天加1. 目的是构造一个可以group by 的东东

[撩天槌]

where convert(datetime,RQ+' '+TIME) between 时间1 and 时间2

[nzperfect]

是不是这个意思?

create table #t1
(
 id int identity(1,1),
 rq varchar(10),
 tm varchar(8)
)

insert #t1 select '2008-05-25','8:12:12'
insert #t1 select '2008-05-26','12:12:12'
insert #t1 select '2008-05-26','15:12:12'
insert #t1 select '2008-05-26','17:12:12'
insert #t1 select '2008-05-27','15:12:12'


select 
case when cast(rq+' '+tm as datetime)>rq+' 14:00:00.000' 
then cast(rq as varchar)+' 14:00:00.000 ' + convert(varchar(10),dateadd(day,1,rq),120)+' 14:00:00.000' 
else convert(varchar(10),dateadd(day,-1,rq),120)+' 14:00:00.000 ' +cast(rq as varchar)+' 14:00:00.000'  end as 时间段,
count(*)
from #t1 where 
cast(rq+' '+tm as datetime)>'2008-5-25 8:12:12'
group by 
    case when cast(rq+' '+tm as datetime)>rq+' 14:00:00.000' 
    then cast(rq as varchar)+' 14:00:00.000 ' + convert(varchar(10),dateadd(day,1,rq),120)+' 14:00:00.000' 
    else convert(varchar(10),dateadd(day,-1,rq),120)+' 14:00:00.000 ' +cast(rq as varchar)+' 14:00:00.000'  end 

/*
时间段  count
2008-05-25 14:00:00.000 2008-05-26 14:00:00.000	1
2008-05-26 14:00:00.000 2008-05-27 14:00:00.000	2
2008-05-27 14:00:00.000 2008-05-28 14:00:00.000	1
*/

[Herb2]

declare @t table(rq char(10),sj char(8),sl int)
insert @t select '2008-05-20','12:00:00',1
insert @t select '2008-05-20','13:00:00',2
insert @t select '2008-05-20','14:00:00',3
insert @t select '2008-05-20','15:00:00',4
insert @t select '2008-05-20','16:00:00',5
insert @t select '2008-05-21','08:00:00',6
insert @t select '2008-05-21','12:00:00',7
insert @t select '2008-05-21','15:00:00',8
insert @t select '2008-05-22','16:00:00',9
declare @sj char(80)
set @sj = '14:00:00'
select riqi = datepart(day,cast(rq+' '+sj as datetime) -@sj),sum(sl) from @t 
group by datepart(day,cast(rq+' '+sj as datetime) -@sj)
/*
riqi                    
----------- ----------- 
19          3
20          25
21          8
22          9
*/

[Limpire]

where cast(TIME as datetime)>='1900-01-01 14:00'