表:1 2005-08-30 10:11:00
2 2005-08-30 10:12:00
3 2005-08-30 12:13:00
4 2005-08-30 20:14:00
5 2005-08-30 20:15:00
6 2005-08-30 23:16:00写sql列出相隔少于1小时的纪录,结果应为:
1 2005-08-30 10:11:00
2 2005-08-30 10:12:00
4 2005-08-30 20:14:00
5 2005-08-30 20:15:00
2 2005-08-30 10:12:00
3 2005-08-30 12:13:00
4 2005-08-30 20:14:00
5 2005-08-30 20:15:00
6 2005-08-30 23:16:00写sql列出相隔少于1小时的纪录,结果应为:
1 2005-08-30 10:11:00
2 2005-08-30 10:12:00
4 2005-08-30 20:14:00
5 2005-08-30 20:15:00
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime)
Insert #T
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'
GoSelect
*
from
#T a
where
exists(select 1 from #T where [Col1]=a.[Col1]+1 and datediff(n,a.[Col2],[Col2])<60)
or
exists(select 1 from #T where [Col1]=a.[Col1]-1 and datediff(n,[Col2],a.[Col2])<60)ol1 Col2
----------- -----------------------
1 2005-08-30 10:11:00.000
2 2005-08-30 10:12:00.000
4 2005-08-30 20:14:00.000
5 2005-08-30 20:15:00.000(4 個資料列受到影響)
create table tb(id int,d datetime)
insert tb
select 1 ,'2005-08-30 10:11:00' union all
select 2 ,'2005-08-30 10:12:00' union all
select 3 ,'2005-08-30 12:13:00' union all
select 4 ,'2005-08-30 20:14:00' union all
select 5 ,'2005-08-30 20:15:00' union all
select 6 ,'2005-08-30 23:16:00'
select * from tb as b
where exists(select * from tb as a where id<>b.id and abs(datediff(hh,a.d,b.d))<=1)drop table tb
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (col int,c2 datetime)
insert into #T
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'--SQL查询如下:select *
from #T t
where DATEDIFF(minute,c2,(select top 1 c2 from #T where col>t.col order by col))<60
or DATEDIFF(minute,(select top 1 c2 from #T where col<t.col order by col desc),c2)<60/*
col c2
----------- -----------------------
1 2005-08-30 10:11:00.000
2 2005-08-30 10:12:00.000
4 2005-08-30 20:14:00.000
5 2005-08-30 20:15:00.000(4 行受影响)*/
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime)
Insert #T
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'
GoSelect
*
from
#T a
where
exists(select 1 from #T where [Col1]=a.[Col1]+1 and datediff(n,a.[Col2],[Col2])<60)
or
exists(select 1 from #T where [Col1]=a.[Col1]-1 and datediff(n,[Col2],a.[Col2])<60)ol1 Col2
----------- -----------------------
1 2005-08-30 10:11:00.000
2 2005-08-30 10:12:00.000
4 2005-08-30 20:14:00.000
5 2005-08-30 20:15:00.000(4 個資料列受到影響)
--> 测试数据: @s
declare @s table (id int,data datetime)
insert into @s
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'select * from @s a
where exists(select * from @s where id=a.id+1 and abs(datediff(hh,a.data,data))<1)
or exists(select * from @s where id=a.id-1 and abs(datediff(hh,a.data,data))<1)
declare @s table (id int,data datetime)
insert into @s
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'select distinct a.* from @s a,@s b,@s c
where (a.id=b.id+1 and abs(datediff(hh,a.data,b.data))<1)
or (a.id=c.id-1 and abs(datediff(hh,a.data,c.data))<1)
INSERT INTO @t
SELECT
1 ,'2005-08-30 10:11:00'
UNION ALL SELECT
2 ,'2005-08-30 10:12:00'
UNION ALL SELECT
3 ,'2005-08-30 12:13:00'
UNION ALL SELECT
4 ,'2005-08-30 20:14:00'
UNION ALL SELECT
5 ,'2005-08-30 20:15:00'
UNION ALL SELECT
6 ,'2005-08-30 23:16:00'
SELECT *
FROM @t a
WHERE DATEDIFF(mi,dt,(SELECT TOP 1 dt FROM @t WHERE ID>a.ID ORDER BY ID ))<=60
OR
DATEDIFF(mi,(SELECT TOP 1 dt FROM @t WHERE ID<a.ID ORDER BY ID DESC),dt)<=60
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime)
Insert #T
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'
Goselect * from #t a
where exists(select 1 from #t where abs(datediff(mi,a.col2,col2)) between 1 and 59)
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-09-11 11:56:10
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(a int,b datetime)
Go
Insert into ta
select 1,'2005-08-30 10:11:00' union all
select 2,'2005-08-30 10:12:00' union all
select 3,'2005-08-30 12:13:00' union all
select 4,'2005-08-30 20:14:00' union all
select 5,'2005-08-30 20:15:00' union all
select 6,'2005-08-30 23:16:00'
Go
--Start
Select *
from ta a
where exists(select 1 from ta where abs(a - a.a) = 1 and abs(datediff(mi,a.b,b)) < 60)
--Result:
/*
a b
----------- ------------------------------------------------------
1 2005-08-30 10:11:00.000
2 2005-08-30 10:12:00.000
4 2005-08-30 20:14:00.000
5 2005-08-30 20:15:00.000(所影响的行数为 4 行)
*/
--End
insert tb
select 1 ,'2005-08-30 10:11:00' union all
select 2 ,'2005-08-30 10:12:00' union all
select 3 ,'2005-08-30 12:13:00' union all
select 4 ,'2005-08-30 20:14:00' union all
select 5 ,'2005-08-30 20:15:00' union all
select 6 ,'2005-08-30 23:16:00'
select * from tb as b
where exists(select * from tb as a where id<>b.id and abs(datediff(hh,a.d,b.d))<=1)drop table tb