--创建示例表

CREATE TABLE t

(

    id INT IDENTITY(1,1) PRIMARY KEY,

    a VARCHAR(10),

    b VARCHAR(10)

)

--插入数据

INSERT INTO t

SELECT 'aa','bb' UNION ALL

SELECT 'a1','bgb' UNION ALL

SELECT 'aa','bb' UNION ALL

SELECT 'a2','bb' UNION ALL

SELECT 'aa3','beeb' UNION ALL

SELECT 'aa','bb' UNION ALL

SELECT 'a2','bb' 

--查询重复数据

SELECT a,b,COUNT(*) count FROM t GROUP BY a,b

--首先通过子查询取出 id 最小的不重复行，然后通过 NOT IN 删除重复数据

DELETE FROM t WHERE id NOT IN(
    SELECT MIN(id) FROM t GROUP BY a,b
)

--首先通过 PARTITION BY 对 a,b 列进行分区排序并生成序号列，然后将序号大于 1 的行删除，即删除了重复数据

DELETE a

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY ID) num FROM t) a

WHERE num>1