我的SQL数据库本来是按日期分类查询的,近斯要分时间段查询。
(其中表里有RQ,TIME字段,我是用RQ+TIME来合并的。但WHERE不会写了。)
如果我将时间段设置成下午14:00,就是说每次查询的时候查每天14:00到次日14:00里的内容
应该用什么方法来实现呢?(目的就是查询每月的以每天14:00为分割点的数据)请高手赐教!
(其中表里有RQ,TIME字段,我是用RQ+TIME来合并的。但WHERE不会写了。)
如果我将时间段设置成下午14:00,就是说每次查询的时候查每天14:00到次日14:00里的内容
应该用什么方法来实现呢?(目的就是查询每月的以每天14:00为分割点的数据)请高手赐教!
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'
这个思路吧
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
*/
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
*/