;with temptb as ( select top 5 * from tb order by id desc ) delete from tempdb
DELETE FROM DATAS WHERE ID IN (SELECT TOP 2 ID FROM DATAS ORDER BY ID DESC) DATAS ID User_ID Data ----------- ----------- -------------------- 1 1 11111111111111111 2 1 22222222222222222 3 1 33333333333333333 4 2 44444444444444444 5 2 55555555555555555结果:ID User_ID Data ----------- ----------- -------------------- 1 1 11111111111111111 2 1 22222222222222222 3 1 33333333333333333(所影响的行数为 2 行)
delete from tb where id not in (select top 10 from tb)
delete from tb where id in (select top 10 id from tb order by id desc)
//先按shujuID(tb表的一个字段名)倒序排列,再选择前3行就可以了。 delete top 3 shuju from tb order by shujuId desc
delete datas where id in (select top 2 id from datas order by id desc)
Coming a sample:--exec dbo.P_getColumns_inrow 'testa',1 --Result empid empname comnameSelect * from testa --Result empid empname comname 1 电脑 NULL 2 科技 NULL 3 教育 NULL 5 术;技; NULL 4 技;术 NULL 6 技术 NULL--Remove bottom 5 of table testabegin tran declare @count int select @count= count(*) from testa select @count=@count-5 --print @count select top (@count) * into testa_temp from testa select * from testa_temp DROP TABLE testa GO exec SP_RENAME 'testa_temp','testa' GO select * from testa rollback --Result empid empname comname 1 电脑 NULL
路过,来拿分的。我也来一句 with delete_tb as (select top 10 * from tb order by id desc ) delete from delete_tb 或者 delete from tb where id not in (select top 10 id from tb order by id desc)
delete from 表名 where 属性名(select top 2 属性名 from 表名 order by 条件)
注意看结果,引9楼数据. 如果删除最后2条记录.用9楼仍然是想要结果,即是empid empname comname 1 电脑 NULL 2 科技 NULL 3 教育 NULL 5 术;技; NULL而 delete testa where empid in (select top 2 empid from testa order by empid desc) ---Resultempid empname comname 1 电脑 NULL 2 科技 NULL 3 教育 NULL 4 技;术 NULL如果给的表有自增列,固然简单.如果没有自己构造个自增列...
Appending.... select * from csdn_temp cid c_no 1 20090501 2 20090502 3 20090503 5 20090505 4 20090504select * into csdn_temp from test_csdn --Append a identity columnalter table csdn_temp add iden int identity--Delete The last two record from table csdn_temp WITh test AS (select * from csdn_temp where iden in (select top 2 iden from csdn_temp order by iden desc)) delete from test;--Drop this identity column alter table csdn_temp drop column iden select * from csdn_tempcid c_no 1 20090501 2 20090502 3 20090503
as
(
select top 5 * from tb
order by id desc
)
delete from tempdb
DATAS
ID User_ID Data
----------- ----------- --------------------
1 1 11111111111111111
2 1 22222222222222222
3 1 33333333333333333
4 2 44444444444444444
5 2 55555555555555555结果:ID User_ID Data
----------- ----------- --------------------
1 1 11111111111111111
2 1 22222222222222222
3 1 33333333333333333(所影响的行数为 2 行)
where id in (select top 10 id from tb order by id desc)
delete top 3 shuju
from tb
order by shujuId desc
where id in (select top 2 id from datas order by id desc)
Coming a sample:--exec dbo.P_getColumns_inrow 'testa',1
--Result
empid empname comnameSelect * from testa
--Result
empid empname comname
1 电脑 NULL
2 科技 NULL
3 教育 NULL
5 术;技; NULL
4 技;术 NULL
6 技术 NULL--Remove bottom 5 of table testabegin tran
declare @count int
select @count= count(*) from testa
select @count=@count-5
--print @count
select top (@count) *
into testa_temp
from testa
select * from testa_temp
DROP TABLE testa
GO
exec SP_RENAME 'testa_temp','testa'
GO
select * from testa
rollback --Result
empid empname comname
1 电脑 NULL
with delete_tb
as
(select top 10 * from tb order by id desc
)
delete from delete_tb
或者
delete from tb where id not in (select top 10 id from tb order by id desc)
注意看结果,引9楼数据.
如果删除最后2条记录.用9楼仍然是想要结果,即是empid empname comname
1 电脑 NULL
2 科技 NULL
3 教育 NULL
5 术;技; NULL而
delete testa
where empid in (select top 2 empid from testa order by empid desc)
---Resultempid empname comname
1 电脑 NULL
2 科技 NULL
3 教育 NULL
4 技;术 NULL如果给的表有自增列,固然简单.如果没有自己构造个自增列...
Appending....
select * from csdn_temp
cid c_no
1 20090501
2 20090502
3 20090503
5 20090505
4 20090504select *
into csdn_temp
from test_csdn --Append a identity columnalter table csdn_temp add iden int identity--Delete The last two record from table csdn_temp
WITh test AS
(select * from csdn_temp where iden in (select top 2 iden from csdn_temp order by iden desc))
delete from test;--Drop this identity column
alter table csdn_temp drop column iden
select * from csdn_tempcid c_no
1 20090501
2 20090502
3 20090503