SQL分组查询如何获取分组内有有效Index值的最新一条记录

现有写法问题说明 #

1. 原查询没有过滤Index非空的记录，直接取最大年月会返回没有Index值的2021年4月数据
2. 子查询排序后外层直接GROUP BY的写法不符合SQL标准，在非严格SQL模式下返回的分组内行是不可控的，所以会出现返回2020年11月数据的异常结果
3. 原查询子查询没有选取Index字段，也无法拿到对应Index值

方案1：MySQL 8.0+/支持窗口函数的数据库（推荐） #

用窗口函数直接按国家分组，过滤非空Index后取最新年月的行：

SELECT year, month, country_id AS value, `Index`
FROM (
    SELECT 
        d.year, 
        d.month, 
        d.country_id,
        d.`Index`,
        -- 按国家分组，非空Index的记录按年月倒序排序
        ROW_NUMBER() OVER (PARTITION BY d.country_id ORDER BY d.year DESC, d.month DESC) AS rn
    FROM `general` d
    INNER JOIN units c ON c.id = d.country_id
    WHERE 
        d.country_id IN (185)
        AND d.`Index` IS NOT NULL -- 过滤掉没有Index值的记录
) AS t
WHERE rn = 1; -- 取每个国家最新的一条记录

该写法逻辑清晰，扩展性强，需要查询多个国家时不需要修改逻辑。

方案2：兼容MySQL 5.x等不支持窗口函数的场景 #

用关联子查询匹配每个国家最大的符合条件的年月：

SELECT d.year, d.month, d.country_id AS value, d.`Index`
FROM `general` d
INNER JOIN units c ON c.id = d.country_id
WHERE 
    d.country_id IN (185)
    AND d.`Index` IS NOT NULL
    -- 匹配当前国家没有比这条记录更新的有Index值的记录
    AND NOT EXISTS (
        SELECT 1 FROM `general` d2
        WHERE 
            d2.country_id = d.country_id 
            AND d2.`Index` IS NOT NULL
            AND (d2.year > d.year OR (d2.year = d.year AND d2.month > d.month))
    );

两种写法都会返回所需的2021年3月、Index为38的记录，多国家查询时也会自动返回每个国家对应的最新有值记录。

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