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

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. 如果“CODE”之一包含空值，则删除该空值。
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