select dateadd(mi,15,max(datecolumn)) from tablename
解决了!谢谢coolingpipe(冷箫轻笛)!
还是先谢谢你coolingpipe(冷箫轻笛)!
可以建一个Temp表,将所有时间写入,再用Temp 与上面的表进行左外联接,求Null值就可以了
create table aa ( dat datetime, id int )insert into aa select '2007-1-24 00:15:00', 1 insert into aa select '2007-1-24 00:45:00', 1 insert into aa select '2007-1-24 01:00:00', 1 insert into aa select '2007-1-24 01:15:00', 2--下次的日期查询 select dateadd(mi,15,max(dat)) from aa--结果 2007-01-24 01:30:00.000--查询数据没有存入的最小时间 select dateadd(mi,15,min(dat)) from aa a where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat)) --结果 2007-01-24 00:30:00.000--查询所有没有存入的时间 select dateadd(mi,15,min(dat)) from aa a where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat)) and exists (select 1 from aa where dat > a.dat)--结果(这个还是只有一条,赫赫) 2007-01-24 00:30:00.000
Temp ( Time ) 2007-1-24 00:15:00 2007-1-24 00:45:00 2007-1-24 01:00:00 2007-1-24 01:15:00 上面的表AA Time,Otherselect Time from Temp Left Outer join AA on Temp.Time=AA.Time where AA.Time is null
没有测试过,
another way: select identity(int,1,1) as intID,date_time,(select datediff(n,min(date_time),a.date_time) from tablename)as diff into #a from tablename aselect * from #a where diff > 15*intIDBut this way is too complex!Thank You! coolingpipe(冷箫轻笛) and Derek_cap!
coolingpipe(冷箫轻笛): 还是你的方法好! 只要把 --查询数据没有存入的最小时间 select dateadd(mi,15,min(dat)) from aa a --该行改为select * from aa a where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat))--查询所有没有存入的时间 select dateadd(mi,15,min(dat)) from aa a --该行改为select * from aa a where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat)) and exists (select 1 from aa where dat > a.dat)
(
dat datetime,
id int
)insert into aa select '2007-1-24 00:15:00', 1
insert into aa select '2007-1-24 00:45:00', 1
insert into aa select '2007-1-24 01:00:00', 1
insert into aa select '2007-1-24 01:15:00', 2--下次的日期查询
select dateadd(mi,15,max(dat)) from aa--结果
2007-01-24 01:30:00.000--查询数据没有存入的最小时间
select dateadd(mi,15,min(dat)) from aa a
where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat))
--结果
2007-01-24 00:30:00.000--查询所有没有存入的时间
select dateadd(mi,15,min(dat)) from aa a
where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat))
and exists (select 1 from aa where dat > a.dat)--结果(这个还是只有一条,赫赫)
2007-01-24 00:30:00.000
2007-1-24 00:15:00
2007-1-24 00:45:00
2007-1-24 01:00:00
2007-1-24 01:15:00
上面的表AA Time,Otherselect Time from Temp Left Outer join AA on Temp.Time=AA.Time
where AA.Time is null
没有测试过,
select identity(int,1,1) as intID,date_time,(select datediff(n,min(date_time),a.date_time) from tablename)as diff into #a from tablename aselect * from #a where diff > 15*intIDBut this way is too complex!Thank You! coolingpipe(冷箫轻笛) and Derek_cap!
还是你的方法好!
只要把
--查询数据没有存入的最小时间
select dateadd(mi,15,min(dat)) from aa a --该行改为select * from aa a
where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat))--查询所有没有存入的时间
select dateadd(mi,15,min(dat)) from aa a --该行改为select * from aa a
where not exists(select 1 from aa where dat = dateadd(mi,15,a.dat))
and exists (select 1 from aa where dat > a.dat)