如何筛选同一PROC_CODE对应最大DATE的VALUE？SQL语法排查

我有一个包含同一医疗操作多组数据的数据集，想要筛选出DATE为最大值对应的VALUE，数据集示例如下：

PROC_CODE | VALUE | DATE
123456      20.90  2020-01-01
123456      30.00  2021-01-01
123456      15.47  2022-06-01

期望仅返回对应最近日期的记录：

PROC_CODE | VALUE | DATE
123456      15.47  2022-06-01

我尝试了以下代码但报错，请问逻辑存在什么问题？

SELECT 
PROC_CODE, VALUE
FROM MY_TABLE
WHERE MAX(DATE)
GROUP BY PROC_CODE -- Only grouping by PROC_CODE because grouping by PROC_CODE and VALUE returns the 3 lines of the dataset

你的SQL逻辑问题分析 #

1. WHERE子句误用聚合函数：WHERE用于筛选单条行数据，不能直接使用MAX(DATE)这类聚合函数。聚合函数的过滤需用HAVING，但此场景不适用HAVING。
2. SELECT与GROUP BY不匹配：SELECT中包含VALUE，但GROUP BY仅指定PROC_CODE。SQL要求非聚合列必须出现在GROUP BY中，否则数据库无法确定返回同一PROC_CODE下的哪一个VALUE。

正确解法 #

方法一：窗口函数（推荐）

利用ROW_NUMBER()窗口函数给每组数据按日期倒序编号，取编号为1的记录：

SELECT PROC_CODE, VALUE, DATE
FROM (
    SELECT 
        PROC_CODE, 
        VALUE, 
        DATE,
        -- 按PROC_CODE分组，每组内按DATE倒序排序生成行号
        ROW_NUMBER() OVER (PARTITION BY PROC_CODE ORDER BY DATE DESC) AS rn
    FROM MY_TABLE
) t
WHERE rn = 1;

方法二：子查询关联

先找出每个PROC_CODE的最新日期，再关联原表获取对应VALUE：

SELECT m.PROC_CODE, m.VALUE, m.DATE
FROM MY_TABLE m
INNER JOIN (
    -- 分组得到每个PROC_CODE的最新日期
    SELECT PROC_CODE, MAX(DATE) AS max_date
    FROM MY_TABLE
    GROUP BY PROC_CODE
) t ON m.PROC_CODE = t.PROC_CODE AND m.DATE = t.max_date;

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