基于日期字段最大值获取两表分组唯一行的SQL查询问题

需求：针对jobnumber为'2099'的数据，按volume和drawing_num分组，每组仅返回对应MAX(rev_date)的一行记录。

当前查询语句 #

SELECT
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    cc_qaqc_drawings.drawing_name,
    MAX(cc_qaqc_drawings_detail.rev_date) AS revdate,
    cc_qaqc_drawings_detail.rev_desc,
    cc_qaqc_drawings_detail.rev_type,
    da.cc_qaqc_drawings.id
FROM da.cc_qaqc_drawings
LEFT JOIN da.cc_qaqc_drawings_detail 
  ON da.cc_qaqc_drawings.id = da.cc_qaqc_drawings_detail.draw_id
WHERE cc_qaqc_drawings.jobnumber = '2099'
GROUP BY
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    cc_qaqc_drawings.drawing_name,
    da.cc_qaqc_drawings.id,
    cc_qaqc_drawings_detail.rev_desc,
    cc_qaqc_drawings_detail.rev_type
ORDER BY
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    MAX(cc_qaqc_drawings_detail.rev_date) DESC;

当前查询结果 #

期望结果 #

问题分析 #

原查询的问题在于：

1. GROUP BY中包含了rev_desc和rev_type，这会导致同一volume+drawing_num下，只要这两个字段值不同，就会被分成不同的组，从而返回多行记录。
2. 即使去掉这两个字段，直接选择rev_desc、rev_type这类非聚合列，数据库无法确定要返回分组内哪一行的对应值，结果具有不确定性。

修正后的查询语句 #

使用窗口函数ROW_NUMBER()是解决这类"每组取最新记录"问题的通用方案，适用于MySQL 8+、PostgreSQL、Oracle等主流数据库：

WITH ranked_drawings AS (
    SELECT
        d.volume,
        d.drawing_num,
        d.drawing_name,
        dd.rev_date,
        dd.rev_desc,
        dd.rev_type,
        d.id,
        -- 按volume和drawing_num分组，每组内按rev_date倒序排序，NULL排最后
        ROW_NUMBER() OVER (
            PARTITION BY d.volume, d.drawing_num
            ORDER BY dd.rev_date DESC NULLS LAST
        ) AS rn
    FROM da.cc_qaqc_drawings d
    LEFT JOIN da.cc_qaqc_drawings_detail dd 
        ON d.id = dd.draw_id
    WHERE d.jobnumber = '2099'
)
SELECT
    volume,
    drawing_num,
    drawing_name,
    rev_date,
    rev_desc,
    rev_type,
    id
FROM ranked_drawings
WHERE rn = 1 -- 取每组排序后的第一条（最新）记录
ORDER BY volume, drawing_num;

说明 #

• PARTITION BY d.volume, d.drawing_num：将数据按volume和drawing_num分组。
• ORDER BY dd.rev_date DESC NULLS LAST：每组内按rev_date倒序排列，确保最新的日期排在最前面；NULLS LAST保证没有修订日期的记录排在分组末尾（但这类记录本身只有一行，所以不影响结果）。
• ROW_NUMBER()给每组内的记录分配序号，取rn=1的记录就是每组的最新行。

如果你的数据库不支持CTE（公共表表达式），可以改用子查询写法：

SELECT
    volume,
    drawing_num,
    drawing_name,
    rev_date,
    rev_desc,
    rev_type,
    id
FROM (
    SELECT
        d.volume,
        d.drawing_num,
        d.drawing_name,
        dd.rev_date,
        dd.rev_desc,
        dd.rev_type,
        d.id,
        ROW_NUMBER() OVER (
            PARTITION BY d.volume, d.drawing_num
            ORDER BY dd.rev_date DESC NULLS LAST
        ) AS rn
    FROM da.cc_qaqc_drawings d
    LEFT JOIN da.cc_qaqc_drawings_detail dd 
        ON d.id = dd.draw_id
    WHERE d.jobnumber = '2099'
) t
WHERE rn = 1
ORDER BY volume, drawing_num;

内容的提问来源于stack exchange，提问作者house