22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT,
n NVARCHAR(20)
)
GO
INSERT INTO t VALUES (1,'abc')
INSERT INTO t VALUES (1,'abc')
INSERT INTO t VALUES (2,'abc')
GO
--- 以上为测试表及测试数据 ---
--公共表表达式
WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY id,n ORDER BY (SELECT 1)) AS rid,*
FROM t
)
--删除
DELETE FROM cte WHERE rid>1
--查看当前数据
SELECT * FROM t
/*
id n
----------- --------------------
1 abc
2 abc
*/
采用上面类似的做法就可以了。