删除问题
id1 id2 level
004 1 1
010 0 1
005 2 2
030 0 3
怎样根据level的大小删除数据(从大到小)
作者: JKSTUDENT 发布时间: 2011-06-09
作者: qianjin036a 发布时间: 2011-06-09
select max(level) from tablename);
作者: shaozengwei 发布时间: 2011-06-09
delete table where level in (select max(level) from table)
作者: flybird66 发布时间: 2011-06-09
delete from tablename where level =(
select max(level) from tablename);
正解
作者: raincg 发布时间: 2011-06-09
作者: fredrickhu 发布时间: 2011-06-09
当level相同时,可以一起删除。
作者: JKSTUDENT 发布时间: 2011-06-09
create table tb(id1 nvarchar(10),id2 int,level int) insert into tb select '004',1,1 insert into tb select '010',0,1 insert into tb select '005',2,2 insert into tb select '030',0,3 go delete a from tb a where not exists(select * from tb where level>a.level) select * from tb /* id1 id2 level ---------- ----------- ----------- 004 1 1 010 0 1 005 2 2 (3 行受影响) */ delete a from tb a where not exists(select * from tb where level>a.level) select * from tb /* id1 id2 level ---------- ----------- ----------- 004 1 1 010 0 1 (2 行受影响) */ delete a from tb a where not exists(select * from tb where level>a.level) select * from tb /* id1 id2 level ---------- ----------- ----------- (0 行受影响) */ go drop table tb
作者: qianjin036a 发布时间: 2011-06-09
declare @table table (id1 varchar(3),id2 int,level int) insert into @table select '004',1,1 union all select '010',0,1 union all select '005',2,2 union all select '030',0,3 select * from @table order by level desc delete from @table where level=(select max(level) from @table)
作者: maco_wang 发布时间: 2011-06-09
作者: JKSTUDENT 发布时间: 2011-06-09
如果有很多数据,逐条删除不现实
不是逐条删除,而是逐批删除,以你的 level 值的顺序.
如果7楼这样不行,那你需要的是什么样的删除法?
作者: qianjin036a 发布时间: 2011-06-09