/* 创建测试数据, 7行中有3行是重复的.*/
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1 UNION ALL
SELECT 1, 1 UNION ALL --duplicate
SELECT 1, 1 UNION ALL --duplicate
SELECT 1, 2 UNION ALL
SELECT 1, 2 UNION ALL --duplicate
SELECT 1, 3 UNION ALL
SELECT 1, 4
GO
/* 查询原始数据 */
SELECT * FROM DuplicateRcordTable
/* 创建公用表达式CTE, 按照表中所有字段分组,增加一列行序号 */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
/* 根据CTE,删除行序号中>1的行, 即重复的行 */
DELETE FROM CTE WHERE DuplicateCount > 1
GO
/* 查询删除后的结果 */
SELECT * FROM DuplicateRcordTable
GO
drop table DuplicateRcordTable