--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 行受影响)
-_-!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 行受影响)
感谢claro回答 上一分钟是显示了 但是我要的效果是这样value time 26 2010-11-16 6:16:23 23 2010-11-16 6:17:23
比如这样的方式,或者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 行受影响)*/
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 行受影响)*/
select value,time from #demo,(select MAX(time)MaxValue from #demo)T where DATEDIFF(SS,#demo.time,T.MaxValue)%60=0
推一个月,要增加一个条件: 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
谢谢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)
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 行受影响)
--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 行受影响)
上一分钟是显示了 但是我要的效果是这样value time
26 2010-11-16 6:16:23
23 2010-11-16 6:17:23
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
(
[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 行受影响)*/
(
[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 行受影响)*/
select value,time from #demo,(select MAX(time)MaxValue from #demo)T where DATEDIFF(SS,#demo.time,T.MaxValue)%60=0
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
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)