sql - 删除重复行 SQL 服务器

Question

我正在使用 SQL Server。我创建的 SQL 表有超过 20000 行。我使用以下查询过滤重复项。

SELECT
    Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate,COUNT(*)
FROM
    TotalsByGLCenter
GROUP BY
    Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate
HAVING 
    COUNT(*) > 1

现在我想从 SQL Server 中删除重复项如何在上面的查询之后添加删除？

Answer 1

;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum
             FROM   TotalsByGLCenter)
    Delete FROM cte
    WHERE  RowNum > 1;

上述查询中的“ROW_NUMBER()”返回结果集分区内行的序号，每个分区中的第一行从 1 开始。 “ORDER BY”子句确定将 ROW_NUMBER 值分配给分区中的行的顺序。这里使用的“PARTITION BY”子句将结果集划分为分区；

新列 RowNum 显示重复行的行号。

如果您想查看重复的行，您可以使用select 而不是delete

   ;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum,Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate 
             FROM   TotalsByGLCenter)
    Select * FROM cte
    WHERE  RowNum > 1;