delete a
from tablename a
where exists (
select 1 from tablename
where iid=a.iid
and 时间<a.时间
and 时间>=datediff(mm,1,a.时间)
)
from tablename a
where exists (
select 1 from tablename
where iid=a.iid
and 时间<a.时间
and 时间>=datediff(mm,1,a.时间)
)
是删除最小时间的那个吗,如果有多个连接相邻的时间,是不是都要删除,只剩下最后的一个时间?
--先查询看看要删除的数据是否正确再删除
--查询语句:
select a.*
from tablename a
where exists (
select 1 from tablename
where iid=a.iid
and 时间<a.时间
and 时间>=datediff(mm,1,a.时间)
)
where exists(Select * from tb
where IID=a.IID and Datediff(minute,时间,a.时间)=1)
你的写法,得出的结果:
1 2006-08-08 08:03:00.000
2 2006-08-08 08:03:00.000
3 2006-08-08 08:03:00.000iid=1在13:01那条没有!
不合要求!
你那种时间就保持第一条!: 1 2006-08-20 18:00:01.000
from tablename a
where exists (
select 1 from tablename
where idi=a.iid
and 时间 < a.时间
and datediff(minute,时间,a.时间)<=1
)
这个好用,好象上面的缩写有问题
你去运行一下了!不合要求!会少了一条记录!
————————————————————————
给你测试的数据:
/*
测试文档*/
/*
create table T
(
iid int,
iTime datetime
)insert into t
select 1,'2006-08-08 08:02:00' union all
select 2,'2006-08-08 08:02:00' union all
select 3,'2006-08-08 08:02:00' union all
select 1,'2006-08-08 08:03:00' union all
select 2,'2006-08-08 08:03:00' union all
select 3,'2006-08-08 08:03:00' union all
select 4,'2006-08-08 12:02:00'
*/
iid 时间
----------- ------------------------------------------------------
1 2006-08-08 08:02:00.000
2 2006-08-08 08:02:00.000
3 2006-08-08 08:02:00.000
4 2006-08-08 12:02:00.000(所影响的行数为 4 行)
insert tableName select
1, '2006-08-20 18:00:01.000' union all select
2, '2006-08-20 18:00:01.000' union all select
3, '2006-08-20 18:00:01.000' union all select
4, '2006-08-20 18:00:01.000' union all select
1, '2006-08-20 18:01:01.000' union all select
2, '2006-08-20 18:01:01.000' union all select
3, '2006-08-20 18:01:01.000' union all select
4, '2006-08-20 18:01:01.000' union all select
1, '2006-08-20 13:00:01.000' union all select
1, '2006-08-20 18:02:01.000' union all select
1, '2006-08-20 18:03:01.000'
godelete a
from tablename a
where exists (
select 1 from tablename
where id=a.id
and is_time < a.is_time
and datediff(minute,is_time,a.is_time)<=1
)select * from tablename
--结果
id is_time
----------- ------------------------------------------------------
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(所影响的行数为 5 行)
from tablename a
where exists (
select 1 from tablename
where iid=a.iid
and 时间<a.时间
and 时间>=dateadd(mm,1,a.时间)
)--写错了函数,呵呵,应该出错的呀
----------- ------------------------------------------------------
1 2006-08-08 08:03:00.000
2 2006-08-08 08:03:00.000
3 2006-08-08 08:03:00.000(所影响的行数为 3 行)