求相差一分钟的时间.txt

来自「SQL语言常用的一些命令各代码」· 文本 代码 · 共 29 行

/*

1	2006-08-20 18:00:01.000
2
	2006-08-20 18:00:01.000
3
	2006-08-20 18:00:01.000
4
	2006-08-20 18:00:01.000
1
	2006-08-20 18:01:01.000
2
	2006-08-20 18:01:01.000
3
	2006-08-20 18:01:01.000
4
	2006-08-20 18:01:01.000
1
	2006-08-20 13:00:01.000
result:
--同一个IID，相差一分钟删除！

1	2006-08-20 18:00:01.000
2
	2006-08-20 18:00:01.000
3
	2006-08-20 18:00:01.000
4
	2006-08-20 18:00:01.000
1
	2006-08-20 13:00:01.000
*/
 
--创建表TIMES

create table times(id int,times datetime)

--插入数据

insert into times select 1,'2006-08-20 18:00:01.000'

insert into times select 1,'2006-08-20 18:01:01.000'

union all         select 2,'2006-08-20 18:00:01.000'union all         select 2,'2006-08-20 18:01:01.000'

union all         select '3',	'2006-08-20 18:00:01.000'

union all         select '3',	'2006-08-20 18:01:01.000'

union all         select '4',	'2006-08-20 18:00:01.000'

union all         select '4',	'2006-08-20 18:01:01.000'

union all         select '1',	'2006-08-20 13:00:01.000'

--解答1


select distinct * from (select id ,times=(select min(times) from times where times<=a.times and datediff(minute,times,a.times)<=1)
from times a) b

--解答2delete a
from times a 
where exists (
select 1 from times
where id=a.id
and times < a.times
and datediff(minute,times,a.times)<=1
)--解答3
Delete A 
  From Test A Join Test B
  On DateDiff(Minute,A._Date,B._Date)=1 and A.SID=B.SID