if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int identity, empid int, date varchar(10), time varchar(10))
insert # select 1,'2008-03-20', '08:25:00'
insert # select 1,'2008-03-20', '08:26:00'
insert # select 1,'2008-03-20', '08:27:00'
insert # select 1,'2008-03-20', '08:35:00'
insert # select 2,'2008-03-20', '08:24:00'
insert # select 2,'2008-03-20', '08:25:00'
insert # select 2,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '17:30:00'begin tran
--> 保留小的
delete # from # a where exists (select 1 from # where empid=a.empid and date=a.date and time<a.time and datediff(minute,time,a.time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
1 1 2008-03-20 08:25:00
4 1 2008-03-20 08:35:00
5 2 2008-03-20 08:24:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
rollback tran--> 保留大的
delete # from # a where exists (select 1 from # where empid = a.empid and date=a.date and time>a.time and datediff(minute,a.time,time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
3 1 2008-03-20 08:27:00
4 1 2008-03-20 08:35:00
6 2 2008-03-20 08:25:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
create table #(id int identity, empid int, date varchar(10), time varchar(10))
insert # select 1,'2008-03-20', '08:25:00'
insert # select 1,'2008-03-20', '08:26:00'
insert # select 1,'2008-03-20', '08:27:00'
insert # select 1,'2008-03-20', '08:35:00'
insert # select 2,'2008-03-20', '08:24:00'
insert # select 2,'2008-03-20', '08:25:00'
insert # select 2,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '17:30:00'begin tran
--> 保留小的
delete # from # a where exists (select 1 from # where empid=a.empid and date=a.date and time<a.time and datediff(minute,time,a.time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
1 1 2008-03-20 08:25:00
4 1 2008-03-20 08:35:00
5 2 2008-03-20 08:24:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
rollback tran--> 保留大的
delete # from # a where exists (select 1 from # where empid = a.empid and date=a.date and time>a.time and datediff(minute,a.time,time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
3 1 2008-03-20 08:27:00
4 1 2008-03-20 08:35:00
6 2 2008-03-20 08:25:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
解决方案 »
- 我快疯了,这个语句为何就是不能执行啊???
- 关于返回值
- SQL 按ID及列内容自动生成序号排序问题
- 索引问题
- 请教从EXECL导数据到SQLSERVER的问题
- 在WinXP professional上安装什么版本的sql server 2000可以?
- (高手请进)批量更新语句对Int类型有效,对Varchar类型无效,奇怪!
- 求一个存储过程,高手进~~~~~
- 该操作未能执行,因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。[OLE/DB provider returned message: 新事务不能登记到指定的事
- 查找流水号,高手请进
- 高手帮忙呀,毕业设计遇到问题了,关于考勤次数统计的
- 求助一个无条件的连接,如何写.
create table #(id int identity, empid int, date varchar(10), time varchar(10))
insert # select 1,'2008-03-20', '08:25:00'
insert # select 1,'2008-03-20', '08:26:00'
insert # select 1,'2008-03-20', '08:27:00'
insert # select 1,'2008-03-20', '08:35:00'
insert # select 2,'2008-03-20', '08:24:00'
insert # select 2,'2008-03-20', '08:25:00'
insert # select 2,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '17:30:00'begin tran
--> 保留小的
delete # from # a where exists (select 1 from # where empid=a.empid and date=a.date and time<a.time and datediff(minute,time,a.time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
1 1 2008-03-20 08:25:00
4 1 2008-03-20 08:35:00
5 2 2008-03-20 08:24:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
rollback tran--> 保留大的
delete # from # a where exists (select 1 from # where empid = a.empid and date=a.date and time>a.time and datediff(minute,a.time,time)<2)
select * from #
/*
id empid date time
----------- ----------- ---------- ----------
3 1 2008-03-20 08:27:00
4 1 2008-03-20 08:35:00
6 2 2008-03-20 08:25:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
id empid date time
----------- ----------- ---------- ----------
1 1 2008-03-20 08:25:00
4 1 2008-03-20 08:35:00
5 2 2008-03-20 08:24:00
7 2 2008-03-20 08:30:00
8 3 2008-03-20 08:30:00
9 3 2008-03-20 17:30:00
*/
两分钟内的只留一条啊,大叔!
如果四条记录各差两分钟那就随便取了,呵呵。一般不会这样,如果真会这样,就取1和3记录就好了。:)Limpire 发的好像是可以了,SQL发觉真是神通广大呀,只有想不到没有做不到,个人感觉 hoo谢谢各位关注啊 我试试 不行再来请教!
你给的语句如果是时间连续相差1分钟有好多条,他只会取第一条呀,比如说1-10连续隔一分钟刷一次,他只取到1,后面的全给删除了呀。。要如何改更好些?
create table #(id int identity, empid int, date varchar(10), time varchar(10))
insert # select 2,'2008-03-20', '08:24:00'
insert # select 1,'2008-03-20', '08:25:00'
insert # select 2,'2008-03-20', '08:25:00'
insert # select 1,'2008-03-20', '08:26:00'
insert # select 1,'2008-03-20', '08:27:00'
insert # select 1,'2008-03-20', '08:28:00'
insert # select 1,'2008-03-20', '08:29:00'
insert # select 2,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '08:30:00'
insert # select 3,'2008-03-20', '17:30:00'
;
--> 2005
with T as
(
select ln=row_number() over (partition by empid,date order by time),* from #
)
select a.empid,a.date,a.time from T a left join T b on a.empid=b.empid and a.date=b.date and a.ln=b.ln+1 and datediff(minute,b.time,a.time)>=2 where b.ln is not null or a.ln%2=1--> 2000
if object_id('tempdb.dbo.#T') is not null drop table #T
select ln=identity(int,1,1),empid,date,time into #T from # order by empid,date,time
select a.empid,a.date,a.time from #T a left join #T b on a.empid=b.empid and a.date=b.date and a.ln=b.ln+1 and datediff(minute,b.time,a.time)>=2 where b.ln is not null or a.ln%2=1/*
empid date time
----------- ---------- ----------
1 2008-03-20 08:25:00
1 2008-03-20 08:27:00
1 2008-03-20 08:29:00
2 2008-03-20 08:25:00
2 2008-03-20 08:30:00
3 2008-03-20 08:30:00
3 2008-03-20 17:30:00
*/
create table #(empid int, date varchar(10), time varchar(10))
insert # select 1,'2008-03-20', '08:25:00'
insert # select 1,'2008-03-20', '08:26:00'
insert # select 1,'2008-03-20', '08:27:00'
insert # select 1,'2008-03-20', '08:29:00'
insert # select 1,'2008-03-20', '08:30:00'
insert # select 1,'2008-03-21', '08:24:00'
insert # select 1,'2008-03-21', '08:26:00'
insert # select 1,'2008-03-21', '08:27:00'
insert # select 1,'2008-03-21', '08:30:00'
insert # select 1,'2008-03-22', '08:30:00'
insert # select 1,'2008-03-22', '17:30:00'if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T(id int identity primary key,gid int,empid int,date varchar(10),time varchar(10),flag int)
insert #T select gid=(select count(1) from # where empid=a.empid and date=a.date and time<=a.time),empid,date,time,null from # a order by 2,3,4
update a set a.flag=a.gid from #T a left join #T b on a.empid=b.empid and a.date=b.date and a.gid=b.gid+1 and datediff(minute,b.time,a.time)>=2 where a.gid=1 or b.gid is not null
select id,empid,date,time from #T a where flag is not null or (flag is null and (gid-(select max(flag) from #T where empid=a.empid and date=a.date and gid<a.gid))%2=0)
/*
id empid date time
----------- ----------- ---------- ----------
1 1 2008-03-20 08:25:00
3 1 2008-03-20 08:27:00
4 1 2008-03-20 08:29:00
6 1 2008-03-21 08:24:00
7 1 2008-03-21 08:26:00
9 1 2008-03-21 08:30:00
10 1 2008-03-22 08:30:00
11 1 2008-03-22 17:30:00
*/