CREATE TABLE [A](
[IDX] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CreateTime] DATETIME not null DEFAULT(getdate()),
[Type] [int] not null,--取值范围[0-10)
[Contents] [varchar](100) not null,
)
CREATE INDEX A_CreateTime ON A (CreateTime)
每个[Type]类型保留最新的100条记录,其余记录全部删除,请写出SQL语句(最多此表有1000条记录)。
select * from A order by CreateTime desc
select* from (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY CreateTime desc) AS rn
FROM A
) tmp
WHERE rn > 100