请教sql按时间间隔查询

[kody008]

create table #demo 
(
	[value] int,
	[time] datetime
)

insert into #demo values('24','2010-11-16 6:15:43');
insert into #demo values('25','2010-11-16 6:15:53');
insert into #demo values('26','2010-11-16 6:16:03');
insert into #demo values('26','2010-11-16 6:16:13');
insert into #demo values('26','2010-11-16 6:16:23');
insert into #demo values('26','2010-11-16 6:16:33');
insert into #demo values('26','2010-11-16 6:16:43');
insert into #demo values('26','2010-11-16 6:16:53');
insert into #demo values('23','2010-11-16 6:17:03');
insert into #demo values('23','2010-11-16 6:17:13');
insert into #demo values('23','2010-11-16 6:17:23');

例如选择按每分间隔查询则显示如下：
value time
26 2010-11-16 6:16:23
23 2010-11-16 6:17:23

即显示最后一个数据和往前推的每分钟一个数据

[claro]

--try
with tbl (value,time) as (
select MAX(value),MAX(time)
from #demo
group by convert(varchar(10),time,20),DATEPART(hh,time),DATEPART(N,time)
)
select value,time from tbl
where DATEDIFF(N,time,'2010-11-16 06:18:20') < 3 --如果显示与指定时间前两条指定where。
--否则是往前推的每分钟一个数据，这里就不要条件判断，因为在tbl里面已经聚合。

--result:
value time
----------- -----------------------
26 2010-11-16 06:16:53.000
23 2010-11-16 06:17:23.000

(2 行受影响)

[claro]

-_-!sorry

--try
with tbl (value,time) as (
select MAX(value),MAX(time)
from #demo
group by convert(varchar(10),time,20),DATEPART(hh,time),DATEPART(N,time)
)
select value,time from tbl
where DATEDIFF(N,time,'2010-11-16 06:18:20') < 3 --如果显示与指定时间前两条指定where。
--否则是往前推的每分钟一个数据，这里就不要条件判断，因为在tbl里面已经聚合。

--result:
value time
----------- -----------------------
26 2010-11-16 06:16:53.000
23 2010-11-16 06:17:23.000

(2 行受影响)

[kody008]

感谢claro回答
上一分钟是显示了 但是我要的效果是这样

value time
26 2010-11-16 6:16:23
23 2010-11-16 6:17:23

[claro]

很显然我郁闷了，如果之前您说明关于秒为何是23的话，我会高兴点。

必须指定23吗？如果是的话，建议在tbl的where条件中指定

[claro]

比如这样的方式，或者table JOIN的方式。

with tbl (value,time) as (
select MAX(value),MAX(time)
from #demo
where DATEPART(s ,time)='23'
group by convert(varchar(10),time,20),DATEPART(hh,time),DATEPART(N,time)
)
select value,time from tbl
where DATEDIFF(N,time,'2010-11-16 06:18:20') < 3

[-晴天]

create table #demo 
(
    [value] int,
    [time] datetime
)

insert into #demo values('24','2010-11-16 6:15:43');
insert into #demo values('25','2010-11-16 6:15:53');
insert into #demo values('26','2010-11-16 6:16:03');
insert into #demo values('26','2010-11-16 6:16:13');
insert into #demo values('26','2010-11-16 6:16:23');
insert into #demo values('26','2010-11-16 6:16:33');
insert into #demo values('26','2010-11-16 6:16:43');
insert into #demo values('26','2010-11-16 6:16:53');
insert into #demo values('23','2010-11-16 6:17:03');
insert into #demo values('23','2010-11-16 6:17:13');
insert into #demo values('23','2010-11-16 6:17:23');
go
select * from #demo sub,(select * from #demo where time=(select min(time) from #demo)) super where datediff(ss,super.time,sub.time)%60=0
go
drop table #demo
/*
value       time                    value       time
----------- ----------------------- ----------- -----------------------
24          2010-11-16 06:15:43.000 24          2010-11-16 06:15:43.000
26          2010-11-16 06:16:43.000 24          2010-11-16 06:15:43.000

(2 行受影响)

*/

[kody008]

你不明我的意思，不是指定23秒
因为数据
2010-11-16 6:17:23
是23秒

那它的前一分钟不就是
2010-11-16 6:16:23
这样吗？

就算其他时间也是一样
应该说是推前一分钟要精确到秒

[-晴天]

create table #demo 
(
    [value] int,
    [time] datetime
)

insert into #demo values('24','2010-11-16 6:15:43');
insert into #demo values('25','2010-11-16 6:15:53');
insert into #demo values('26','2010-11-16 6:16:03');
insert into #demo values('26','2010-11-16 6:16:13');
insert into #demo values('26','2010-11-16 6:16:23');
insert into #demo values('26','2010-11-16 6:16:33');
insert into #demo values('26','2010-11-16 6:16:43');
insert into #demo values('26','2010-11-16 6:16:53');
insert into #demo values('23','2010-11-16 6:17:03');
insert into #demo values('23','2010-11-16 6:17:13');
insert into #demo values('23','2010-11-16 6:17:23');
go
select sub.[value],sub.[time] from #demo sub,(select * from #demo where time=(select max(time) from #demo)) super where datediff(ss,super.time,sub.time)%60=0
go
drop table #demo
/*
value       time
----------- -----------------------
26          2010-11-16 06:16:23.000
23          2010-11-16 06:17:23.000

(2 行受影响)

*/

[tjj5203]

select value,time from #demo,(select MAX(time)MaxValue from #demo)T where DATEDIFF(SS,#demo.time,T.MaxValue)%60=0

[kody008]

谢谢qianjin036a

那按照这样的数据规律

我要以最后数据的时间和往前推1小时的数据又怎样差呢？

还有按天、月为时间间隔

[-晴天]

推1小时,把
datediff(ss,super.time,sub.time)%60
改为
datediff(ss,super.time,sub.time)%3600
推 一天
改为
datediff(ss,super.time,sub.time)%86400

[-晴天]

推一个月,要增加一个条件:
datediff(ss,super.time,sub.time)%60=0 and datediff(mm,super.time,sub.time)=1

[-晴天]

呵呵,写快了,推一个月,要增加一个条件:
datediff(ss,super.time,sub.time)%86400=0 and datediff(mm,super.time,sub.time)=1

[kody008]

谢谢qianjin036a

现在结贴，还有一个sql查询另开贴

[-晴天]

测试了一下,如果是一个月的话,只加一个条件还不够,应该:
datediff(ss,super.time,sub.time)%86400=0 and datediff(mm,super.time,sub.time)=1 and right(convert(varchar(10),super.time),2)=right(convert(varchar(10),sub.time),2)

[kody008]

谢谢qianjin036a