SQL 查询 - 删除具有重复列值的行

Question

我需要能够删除表中的某些行，其中两列组合具有相同的值。例如，在下面的示例表中，应该只有一个组合 (48983, 2018-05-01)。

ID      CertID   DueDate
676790  48983   2018-05-03
678064  48983   2018-05-02
678086  48983   2018-05-01
678107  48983   2018-05-01
678061  48983   2018-05-01

我试图获取重复条目的列表，但我得到的是整个表格。这是我使用的:

WITH A   -- Get a list of unique combinations of ResponseDueDate and CertificateID
AS  (
   SELECT Distinct
          ID,       
          ResponseDueDate,
          CertID
   FROM  FacCompliance
)
,   B  -- Get a list of all those CertID values that have more than one ResponseDueDate associated
AS  (
    SELECT CertID
    FROM   A
    GROUP BY
           CertID
    HAVING COUNT(*) > 1
)
SELECT  A.ID,
        A.ResponseDueDate,
        A.FacCertificateID
FROM    A
    JOIN B
        ON  A.CertID = B.CertID
order by CertID, ResponseDueDate;

我正在使用的查询有什么问题，是否可以删除额外的行(在上面的示例中，保留一个 (48983, 2018-05-01) 组合的实例并删除其余的。我使用的是 SQL Server 2016 .

Answer 1

使用行号:

WITH A AS  (
   SELECT 
          ID,       
          ResponseDueDate,
          CertID,
          ROW_NUMBER() over (partition by CertID, ResponseDueDate order by ResponseDueDate) lp
   FROM  FacCompliance
)
delete a
where lp <> 1
;

此外，如果 ID 是唯一的，您可以在没有窗口函数的情况下做到这一点:

delete fc
from  FacCompliance fc
where exists (
    select 1
    from FacCompliance ref
    where ref.ResponseDueDate = fc.ResponseDueDate
        and ref.CertID = fc.CertID
        and ref.ID < fc.ID
)