sql - 基于 Group By 删除重复项 - SQL

Question

编辑:我想我现在有了解决方案，但需要做更多的意义检查......

DELETE TBLFIRE_TEMP3 FROM TBLFIRE_TEMP3
LEFT OUTER JOIN (
   SELECT MIN(FireNo) as FireNo, ActionRef, FRADate, FIREUPRN
   FROM TBLFIRE_TEMP3 
   GROUP BY ActionRef, FRADate, FIREUPRN
) as KeepRows ON
   TBLFIRE_TEMP3.FireNo = KeepRows.FireNo
WHERE
   KeepRows.FireNo IS NULL

-############### 之前的评论###############

我有一个表，其中有重复项(基于三列)。我可以通过执行以下操作找到它们并查看它们，然后只想删除重复项(即所有计数(*)结果都是“1”)

SELECT COUNT(*),ActionRef, FRADate, FIREUPRN
FROM TBLTempTable
GROUP BY ActionRef, FRADate, FIREUPRN

所以我可以看到这些组出现的次数。我想要做的是删除重复项。我试过下面的但它删除了每一行，甚至是单数:

DELETE a FROM TblTempTable a JOIN
(
  SELECT ActionRef, FRADate, FIREUPRN
    FROM TblTempTable 
   GROUP BY ActionRef, FRADate, FIREUPRN
) d 
   ON (a.ActionRef = b.ActionRef
  AND a.FRADate = b.FRADate
AND a.FIREUPRN = b.FIREUPRN)

根据我看过指南的代码，我相信我已经接近了，但目前它删除了所有内容。

引用资料:SQL- How can I remove duplicate rows? GROUP BY does not remove duplicates

-这些是 MySQL，所以最终不相关:

select and delete rows within groups using mysql Find duplicate records in MySQL

Answer 1

一个简单的解决方案是使用带有 ROW_NUMBER 的 CTE:

WITH Data AS
(
    SELECT RN  = ROW_NUMBER() OVER (PARTITION BY ActionRef, FRADate, FIREUPRN
                                    ORDER BY FRADate ASC),
           Cnt = COUNT(*) OVER (PARTITION BY ActionRef, FRADate, FIREUPRN),
           ActionRef, FRADate, FIREUPRN
    FROM TBLTempTable
)
DELETE FROM Data
WHERE RN > 1

这会删除除一个以外的所有内容，它会保留最旧的 FRADate。您需要更改 ROW_NUMBER 中的 ORDER BY 才能更改此逻辑。

CTE 的一个优点是您可以轻松更改它以查看要删除(或更新)的内容。因此，您只需将 DELETE FROM Data 替换为 SELECT * FROM Data。