如果两列数据匹配，则从SQL表中删除1个重复行[Duplicate]

Question

我的表中有三列

键名、数字、数据

数据由大量重复的条目组成。如果重复"KeyName"和"Data"，我需要从表中的每个重复条目中删除1行。如何实现这一点SQL查询。

Answer 1

您可以使用row\u number（）为每对保留一行：

with todelete as (
      select t.*,
             row_number() over (partition by keyname, data order by (select NULL)) as seqnum
      from t
     )
delete
from todelete
where seqnum > 1;

与任何此类操作一样，删除前应先检查。这很简单：

with todelete as (
      select t.*,
             row_number() over (partition by keyname, data order by (select NULL)) as seqnum
      from t
     )
select *
from todelete
where seqnum > 1;

Answer 2

这应该可以

WITH CTE
    AS (SELECT KeyName
            , Number
            , Data
            , [rn] = ROW_NUMBER() OVER(PARTITION BY KeyName
                                          , Data ORDER BY KeyName
                                                     , Data)
        FROM   yourtable)
     /* UNCOMMENT BELOW TO DELETE */
     --DELETE
    --FROM   CTE
    --WHERE  rn > 1;

    SELECT *
    FROM   CTE
    WHERE  rn > 1;