34,594
社区成员
发帖
与我相关
我的任务
分享
delete t
from (select *,rn=row_number() over(partition by a,b order by getdate()) from [table]) t
where t.rn>1
--测试数据
if object_ID('tempdb..#A') is not null
drop table #A
Go
CREATE TABLE #A
(
a INT ,
stat NVARCHAR(100)
)
Insert #A
select 1,'测试1' UNION ALL
select 2,'测试2' UNION ALL
select 1,'测试1' UNION ALL
select 3,'测试3'
--测试数据结束
;WITH temp AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY a) AS num FROM #A
)
--删除语句
DELETE FROM temp WHERE num >1
--读取测试结果
SELECT * FROM #A