DB2：基于new_date与upd_date获取最新数据集的方法

现有表A，需针对article_id和code_id的每个组合，取new_date与upd_date中较晚日期对应的最新数据。

示例数据 #

| id | article_id | code_id |       new_date      |       upd_date      |
|----|------------|---------|---------------------|---------------------|
| 1  |     a      |     z   | 2023-05-31 12:22:55 |        (null)       |
| 2  |     a      |     z   | 2023-05-15 12:22:55 | 2023-05-17 12:22:55 |
| 3  |     a      |     y   | 2023-05-15 12:22:55 | 2023-05-17 12:22:55 |
...
| 4  |     b      |     y   | 2023-05-20 12:22:55 | 2023-05-21 12:22:55 |
| 5  |     b      |     z   | 2023-05-10 12:22:55 | 2023-05-31 12:22:55 |
| 6  |     b      |     y   | 2023-05-15 12:22:55 | 2023-07-01 12:22:55 |

期望输出 #

| id | article_id | code_id |       new_date      |       upd_date      |
|----|------------|---------|---------------------|---------------------|
| 1  |     a      |    z    | 2023-05-31 12:22:55 |        (null)       |
| 3  |     a      |    y    | 2023-05-15 12:22:55 | 2023-05-17 12:22:55 |
...
| 5  |     b      |    z    | 2023-05-10 12:22:55 | 2023-05-31 12:22:55 |
| 6  |     b      |    y    | 2023-05-15 12:22:55 | 2023-07-01 12:22:55 |

用户尝试的SQL（仅基于new_date筛选） #

SELECT 
  a.id, 
  a.code_id, 
  a.article_id, 
  a.new_date, 
  a.upd_date
FROM A a
INNER JOIN 
   (SELECT 
      code_id, 
      article_id, 
      MAX (new_date) AS MAX_NEW_DATE
    FROM A 
    GROUP BY 
        code_id, 
        article_id) temp
ON a.code_id = temp.code_id 
AND a.new_date = temp.MAX_NEW_DATE

JOIN reference_code code 
ON a.code_id = code.code_id 
WHERE article_id = 'a'

解决方案 #

使用窗口函数ROW_NUMBER()是最直观的实现方式，结合GREATEST()函数处理两个日期的取大逻辑（自动忽略null值，比如upd_date为null时直接取new_date），具体SQL如下：

SELECT id, article_id, code_id, new_date, upd_date
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY article_id, code_id 
            ORDER BY GREATEST(new_date, upd_date) DESC
        ) AS rn
    FROM A
) t
WHERE rn = 1
-- 如需关联reference_code和筛选特定article_id，可添加如下语句
-- JOIN reference_code code ON t.code_id = code.code_id
-- WHERE t.article_id = 'a'

逻辑说明 #

1. PARTITION BY article_id, code_id：按article_id和code_id的组合分组，确保每个组合单独处理。
2. ORDER BY GREATEST(new_date, upd_date) DESC：对每个分组内的行，按new_date和upd_date中的较晚日期倒序排列，最新的行排在最前面。
3. ROW_NUMBER()：为每个分组内的行分配序号，序号为1的就是该组合的最新数据。
4. 外层筛选rn = 1即可得到所有组合的最新数据。

如果你的SQL方言不支持GREATEST()（比如部分旧版数据库），可以用CASE语句替代排序逻辑：

ORDER BY 
    CASE 
        WHEN upd_date IS NULL THEN new_date
        WHEN new_date IS NULL THEN upd_date
        ELSE CASE WHEN new_date > upd_date THEN new_date ELSE upd_date END
    END DESC

内容的提问来源于stack exchange，提问作者Eigenartiger Ä Thubi