SqlServer从[Type]字段的各个类别中分别保留最新的5条记录，其他的全部删除

SqlServer从[Type]字段的各个类别中分别保留最新的5条记录，其他的全部删除

转载:http://helpserver.chiwudaili.com/detail.aspx?id=b719179971b3502ba38155b407ec63f8

 

--CREATE DATABASE [Test] 
Use [Test]

CREATE TABLE [IPPool]
(
[IDX] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CreateTime] [datetime] NOT NULL default(getdate()),
[Type] [int] NOT NULL default(0),
[Content] [varchar](50) NOT NULL,
)
create index IPPool_Type on IPPool([Type])

INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0001')
INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0002')
INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0003')
INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0004')
INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0005')
INSERT INTO [IPPool]([Type],[Content])VALUES(0,'test0006')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0007')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0008')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0009')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0010')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0011')
INSERT INTO [IPPool]([Type],[Content])VALUES(1,'test0012')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0013')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0014')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0015')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0016')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0017')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0018')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0019')
INSERT INTO [IPPool]([Type],[Content])VALUES(2,'test0020')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0021')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0022')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0023')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0024')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0025')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0026')
INSERT INTO [IPPool]([Type],[Content])VALUES(3,'test0027')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0028')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0029')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0030')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0031')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0032')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0033')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0034')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0035')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0036')
INSERT INTO [IPPool]([Type],[Content])VALUES(4,'test0037')

select * from [IPPool] order by [idx]

declare @KeepCount int
set @KeepCount = 5
--select * from 
delete 
[IPPool] where [idx] not in
(
select 
[IDX]
from (
select 
*,row_number() 
over (
partition by [Type] order by [Idx] desc
) rn 
from [IPPool]
) t
where rn<=(@KeepCount)
)

select * from [IPPool] order by [idx]