select * from tb a
where exists (select 1 from tb where bh=a.bh
and rq=a.rq and
xh=a.xh and
convert(char(10),sj,120)=convert(char(10),a.sj,120)
and datepart(hh,sj)=datepart(hh,a.sj)
and datepart(mi,sj)=datepart(mi,a.sj)
and datepart(ss,sj)-datepart(ss,a.sj)<60
and sj>a.sj)
where exists (select 1 from tb where bh=a.bh
and rq=a.rq and
xh=a.xh and
convert(char(10),sj,120)=convert(char(10),a.sj,120)
and datepart(hh,sj)=datepart(hh,a.sj)
and datepart(mi,sj)=datepart(mi,a.sj)
and datepart(ss,sj)-datepart(ss,a.sj)<60
and sj>a.sj)
解决方案 »
- 分页存储过程问题
- 如何在以月份命名建表呀?
- 求教怎么可以让sql server2000企业管理器,表text数据类型的字段值显示出来
- 【大型数据库设计问题抉择!】
- 如何让一个帐号只有create/modify 视图的权限?而不能有其他如create table, create database,.....的权限.
- wangdehao help
- 一个难题求解
- 请各位大神帮忙解答。
- 新手问题:SQL 2K个人版,开发版,企业版有什么区别?
- 请教Oracle顶尖级高手,字符集问题
- 求助,SQL不能注册远程服务器。。。。。
- 在线问题:rollback怎么用?execute后的事务缺少commit或rollback transation语句,原计数=1,当前计数=0
where not exists (select 1 from tb where bh=a.bh
and rq=a.rq and
xh=a.xh and
convert(char(10),sj,120)=convert(char(10),a.sj,120)
and datepart(hh,sj)=datepart(hh,a.sj)
and datepart(mi,sj)=datepart(mi,a.sj)
and datepart(ss,sj)-datepart(ss,a.sj)<60
and sj>a.sj)truncate table tbinsert into tb select * from #tt
delete a from tb a
where exists(
select * from tb
where bh=a.bh and rq=a.rq and xh=a.xh
and sj>dateadd(minute,-1,a.sj)
and sj<a.sj)
create table tb(bh int,rq datetime,sj datetime,xh int)
insert tb select 1,'2004-12-26 00:00:00','1900-01-01 07:28:13',3
union all select 1,'2004-12-26 00:00:00','1900-01-01 07:28:14',3
union all select 1,'2004-12-26 00:00:00','1900-01-01 07:28:16',3
union all select 1,'2004-12-26 00:00:00','1900-01-01 07:30:15',4
union all select 1,'2004-12-26 00:00:00','1900-01-01 17:28:17',5
union all select 1,'2004-12-26 00:00:00','1900-01-01 17:28:18',5
union all select 1,'2004-12-26 00:00:00','1900-01-01 17:28:19',6
union all select 1,'2004-12-26 00:00:00','1900-01-01 17:28:30',6
union all select 2,'2004-12-27 00:00:00','1900-01-01 08:28:13',3
union all select 2,'2004-12-27 00:00:00','1900-01-01 12:28:16',3
union all select 2,'2004-12-27 00:00:00','1900-01-01 08:32:14',3
union all select 2,'2004-12-27 00:00:00','1900-01-01 12:30:15',4
union all select 2,'2004-12-27 00:00:00','1900-01-01 13:28:17',5
union all select 2,'2004-12-27 00:00:00','1900-01-01 13:28:18',5
union all select 2,'2004-12-27 00:00:00','1900-01-01 17:28:19',6
union all select 2,'2004-12-27 00:00:00','1900-01-01 17:28:30',6
go--删除
delete a from tb a
where exists(
select * from tb
where bh=a.bh and rq=a.rq and xh=a.xh
and sj>dateadd(minute,-1,a.sj)
and sj<a.sj)--显示删除结果
select * from tb
go--删除测试
drop table tb/*--测试结果bh rq sj xh
----- ------------------------ ------------------------ ----
1 2004-12-26 00:00:00.000 1900-01-01 07:28:13.000 3
1 2004-12-26 00:00:00.000 1900-01-01 07:30:15.000 4
1 2004-12-26 00:00:00.000 1900-01-01 17:28:17.000 5
1 2004-12-26 00:00:00.000 1900-01-01 17:28:19.000 6
2 2004-12-27 00:00:00.000 1900-01-01 08:28:13.000 3
2 2004-12-27 00:00:00.000 1900-01-01 12:28:16.000 3 --这条不应该删除,已超过几小时了
2 2004-12-27 00:00:00.000 1900-01-01 08:32:14.000 3
2 2004-12-27 00:00:00.000 1900-01-01 12:30:15.000 4
2 2004-12-27 00:00:00.000 1900-01-01 13:28:17.000 5
2 2004-12-27 00:00:00.000 1900-01-01 17:28:19.000 6(所影响的行数为 10 行)
--*/