根据其他列的条件删除重复行

Question

我有一张这样的桌子：

ID       | CODE     | DATE
1        | 2398     | 2016-4-3   
1        | null     | 2015-8-3   
2        | 1942     | 2015-9-8   
3        | 6752     | 2013-2-1   
3        | 7217     | 2015-1-1   
4        | 9827     | 2011-2-9

"ID“中有重复项，我希望根据以下条件删除重复项行：

1. 如果其中一个“代码”包含空，则删除空。
2. 如果这两种代码都包含实际代码，则保留有最新日期的代码。
3. 如果两者都包含空值，则保留带有最新日期的值。

所需的输出如下所示：

ID       | CODE     | DATE
1        | 2398     | 2016-4-3     
2        | 1942     | 2015-9-8      
3        | 7217     | 2015-1-1   
4        | 9827     | 2011-2-9

我知道根据一列删除副本的方法：

WITH CTE AS
(
   SELECT *,
          RN = ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
   FROM dbo.YourTable
)
DELETE FROM CTE
WHERE RN > 1

但我不知道该怎么补充我的条件，有人能帮忙吗？

Answer 1

下面查询的关键是使用解析函数计算以下数量：

COUNT(*) OVER (PARTITION BY ID) - COUNT(CODE) OVER (PARTITION BY ID)

对于重复的只有一个NULL代码的情况，这个数量将等于一个。在大多数其他情况下，这个数量要么是两个(两个代码都是NULL)，要么是零(这两个代码都不是NULL，或者仅仅是一个非NULL代码)。

这允许我们确定是从单个记录还是复制记录中获取最新记录，还是只保留一对副本中的非NULL代码。

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) rn,
        COUNT(*) OVER (PARTITION BY ID) AS total_cnt,
        COUNT(CODE) OVER (PARTITION BY ID) id_cnt
    FROM yourTable
)

DELETE
FROM cte
WHERE
    (total_cnt - id_cnt <> 1 AND rn > 1) OR
    (total_cnt - id_cnt = 1 AND total_cnt > 1 AND CODE IS NULL);

​

Demo

​

Answer 2

您只需使用ORDER BY

WITH CTE AS (
     SELECT t.*,
            ROW_NUMBER() OVER (PARTITION BY COLUMN
                               ORDER BY (CASE WHEN Code IS NOT NULL THEN 1 ELSE 2 END),  -- valid codes first
                                         DATE DESC
                              ) as seqnum
     FROM dbo.YourTable t
    )
DELETE FROM CTE
WHERE seqnum > 1;

order指定的第一行将有一个有效的代码--如果存在的话--以及最近的日期。

Answer 3

Postgres不允许在CTE上删除

首先，对所有三种情况进行编码：

DELETE FROM thistable d
WHERE code IS NULL
        AND EXISTS ( SELECT * FROM thistable x
        WHERE x.id = d.id AND x.code IS NOT NULL
        )
OR code IS NULL
        AND EXISTS ( SELECT * FROM thistable x
        WHERE x.id = d.id AND x.code IS NULL
        AND x.zdate > d.zdate
        )
OR code IS NOT NULL
        AND EXISTS ( SELECT * FROM thistable x
        WHERE x.id = d.id AND x.code IS NOT NULL
        AND x.zdate > d.zdate
        );

现在，您可以组合前两个条件(甚至是第三个条件)。

DELETE FROM thistable d
WHERE code IS NULL
        AND EXISTS ( SELECT * FROM thistable x
        WHERE x.id = d.id
              -- I Don't think you need this
              -- AND x.code <> d.code
        AND x.zdate > d.zdate
        )
OR code IS NOT NULL
        AND EXISTS ( SELECT * FROM thistable x
        WHERE x.id = d.id AND x.code IS NOT NULL
        AND x.zdate > d.zdate
        );

~