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
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,3select * from @table order by level desc delete from @table where level=(select max(level) from @table)
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,3select * from @table order by level desc delete from @table where level=(select max(level) from @table)
select max(level) from tablename);
当level相同时,可以一起删除。
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
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,3select * from @table order by level desc
delete from @table where level=(select max(level) from @table)
如果7楼这样不行,那你需要的是什么样的删除法?
insert into @table
select '004',1,1 union all
select '010',0,1 union all
select '005',2,2 union all
select '030',0,3select * from @table order by level desc
delete from @table where level=(select max(level) from @table)