如何用单条SQL查询获取每组最新状态及是否存在指定状态

现有如下结构的表格数据：

需要实现两个需求：

1. 获取每个Name对应的最新Status，当前尝试的SQL为：

SELECT MAX(Date), Status, Name
FROM test_table
GROUP BY Status, Name

2. 同时判断该Name是否曾有Status为2的记录，当前尝试用CTE实现：

WITH has_2_table AS (
SELECT DISTINCT Name, TRUE as has_2
FROM test_table
WHERE Status = 2 )

原本打算通过Name左关联两个结果，但这种方式比较繁琐，后续还要添加更多字段和检查条件，想找更高效的单条SQL实现方案。

可以用窗口函数结合聚合函数在单条SQL中完成需求，既高效又简洁：

SELECT 
    Name,
    MAX(CASE WHEN rn = 1 THEN Status END) AS latest_status,
    MAX(CASE WHEN Status = 2 THEN TRUE ELSE FALSE END) AS has_2
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date DESC) AS rn
    FROM test_table
) t
GROUP BY Name;

逻辑说明： #

• 内层子查询用ROW_NUMBER()窗口函数，按Name分组、Date倒序排序，给每条记录标记序号rn，序号为1的就是该用户最新的记录。
• 外层通过GROUP BY Name聚合，用MAX(CASE...)提取最新的Status；同时用另一个MAX(CASE...)判断该用户是否存在Status=2的记录——只要有一条满足，就会返回TRUE。

如果你的数据库支持BOOL类型，还可以简化has_2的判断逻辑：

SELECT 
    Name,
    MAX(CASE WHEN rn = 1 THEN Status END) AS latest_status,
    BOOL_OR(Status = 2) AS has_2
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date DESC) AS rn
    FROM test_table
) t
GROUP BY Name;

原SQL的问题说明 #

你之前的GROUP BY Status, Name会把同一个用户的不同Status分开分组，无法正确获取最新的Status——因为MAX(Date)和Status不一定来自同一条记录，而窗口函数的方式能确保拿到最新日期对应的状态。

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