--结果有错.
应为:
1 2008-03-25 1900-01-01 10:25 01
2 2008-03-25 1900-01-01 17:26 01
3 2008-03-25 1900-01-01 19:00 03 delete tb from tb t where jqid not in (select min(jqid) from tb where id = t.id)
应为:
1 2008-03-25 1900-01-01 10:25 01
2 2008-03-25 1900-01-01 17:26 01
3 2008-03-25 1900-01-01 19:00 03 delete tb from tb t where jqid not in (select min(jqid) from tb where id = t.id)
解决方案 »
- --悲哀:写文档的技术太差了,大家看看:有哪里说的不对的地方,帮我改正!先谢谢了!--
- 这条语句的意思??
- 先谢谢大家了!想问怎样改变一张表的所有者。
- sql 里的循环语句,怎么会出现死循环?
- 看似很简单select的查询问题
- 40分在线等:SQL Developer转移SQL Server Express问题求助
- 通过网络连接Sql server,连接不上的问题
- 如何对数据库结构进行调整
- [馨郁星愿]如何根据某个字段的数据(日期类型),把它转换成该数据所对应的星期几??在SQL里怎么写这语句?
- 如何在触发器中删除按时间排序的第2001个记录往后的所有记录?
- 50分求ASP网页,能判断MSSQL数据库中的帐号ID达到一定数量即返回指定页面
- tempdb系統資料庫中暫存資料表
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(2, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(2, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(3, '2008-03-25', '1900-01-01 19:00', '03')
godelete tb from tb t where jqid not in (select min(jqid) from tb where id = t.id) select * from tbdrop table tb/*
id rq sj jqid
----------- ---------- ------------------------------------------------------ ----------
1 2008-03-25 1900-01-01 10:25:00.000 01
2 2008-03-25 1900-01-01 17:26:00.000 01
3 2008-03-25 1900-01-01 19:00:00.000 03
*/
create table tb(id int, rq varchar(10) , sj datetime, jqid varchar(10))
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(2, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(2, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(3, '2008-03-25', '1900-01-01 19:00', '03')
godelete tb from tb t where jqid not in (select min(jqid) from tb where id = t.id and rq = t.rq) select * from tbdrop table tb/*
id rq sj jqid
----------- ---------- ------------------------------------------------------ ----------
1 2008-03-25 1900-01-01 10:25:00.000 01
2 2008-03-25 1900-01-01 17:26:00.000 01
3 2008-03-25 1900-01-01 19:00:00.000 03
*/
我可能没说清:
.......
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(1, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 19:00', '03')
.......
该怎么办?
--如果要算上日期.
create table tb(id int, rq varchar(10) , sj datetime, jqid varchar(10))
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(2, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(2, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(3, '2008-03-25', '1900-01-01 19:00', '03')
godelete tb from tb t where jqid not in (select min(jqid) from tb where id = t.id and rq = t.rq) select * from tbdrop table tb/////********************//////
这样不能达到楼主的目的哦,楼主是要求sj字段中五分钟之内只保留一条记录!
go
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(1, '2008-03-25', '1900-01-01 10:31', '04')
insert into tb values(1, '2008-03-25', '1900-01-01 10:33', '05')
insert into tb values(2, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(2, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(3, '2008-03-25', '1900-01-01 19:00', '03')
go
delete x from tb x
left join
(select id,min(jqid) mjqid ,ceiling((datediff(mi,msj,sj)*1.0+1)/5) gid from tb a
inner join
(select min(sj) msj,id mid from tb group by id) b
on id=mid
group by id, ceiling((datediff(mi,msj,sj)*1.0+1)/5)
) y
on x.id=y.id and mjqid=jqid
where gid is nullselect * from tb
/*
1 2008-03-25 1900-01-01 10:25:00.000 01
1 2008-03-25 1900-01-01 10:30:00.000 03
2 2008-03-25 1900-01-01 17:26:00.000 01
3 2008-03-25 1900-01-01 19:00:00.000 03*/
go
drop table tb
go
create table tb(id int, rq varchar(10) , sj datetime, jqid varchar(10)) insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(1, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 19:00', '03')
go
delete tb
where sj not in
( select (select min(b.sj) from tb b where datediff(second,a.sj,b.sj) between -300 and 0)as groups
from tb a
)
go
select * from tb
drop table tb
/*
id rq sj jqid
----------- ---------- ----------------------- ----------
1 2008-03-25 1900-01-01 10:25:00.000 01
1 2008-03-25 1900-01-01 17:26:00.000 01
1 2008-03-25 1900-01-01 19:00:00.000 03
*/
-- datediff(second,a.sj,b.sj) between -300 and 0) 表示5分钟即300秒
insert into tb values(1, '2008-03-25', '1900-01-01 10:25', '06')
insert into tb values(1, '2008-03-25', '1900-01-01 10:27', '02')
insert into tb values(1, '2008-03-25', '1900-01-01 10:30', '03')
insert into tb values(1, '2008-03-25', '1900-01-01 10:31', '04')
insert into tb values(1, '2008-03-25', '1900-01-01 10:33', '05')
insert into tb values(1, '2008-03-25', '1900-01-01 17:36', '01')
insert into tb values(1, '2008-03-25', '1900-01-01 17:30', '02')
insert into tb values(3, '2008-03-25', '1900-01-01 19:00', '03')
insert into tb values(2, '2008-03-25', '1900-01-01 17:26', '01')
insert into tb values(2, '2008-03-25', '1900-01-01 17:30', '02')
....
出现连环时 需要运行多遍!!