想要根据表中的某一个字段或者是其他几个字段删除重复的数据并且保留一条,可以用CTE+row_number的方式做删除
例子:
--创建临时表
CREATE TABLE #tt
(
id INT IDENTITY(1, 1)
,aa NVARCHAR(5)
);
--插入数据
INSERT INTO #tt
( aa )
VALUES ( 'aaa' ),
( 'aaa' ),
( 'aaa' ),
( 'ab' ),
( 'ab' ),
( 'bb' ),
( 'cc' )
--查看
SELECT *
FROM #tt;
查看数据
--删除重复数据
;WITH cte
AS (
SELECT t.id
,ROW_NUMBER() OVER ( PARTITION BY t.aa ORDER BY t.id ) AS BB
FROM #tt AS t
)
DELETE cte
WHERE cte.BB > 1;
删除后查看数据