SQL实现分组查询字段值首次为0的最早记录行

现有存储马匹价值随时间衰减数据的记录表，马匹价值随时间推移逐步降低。需实现查询逻辑：提取每匹马价值首次变为0对应的记录行——每匹马均存在多条价值为0的记录，仅需返回其中时间最早的一条，返回结果需包含rowID及全部字段。

本次测试数据中符合要求的是rowID为8和20的两行。

之前使用窗口函数未得到正确结果，通常是两个原因：一是没有先过滤value=0的记录就直接排序，导致非0记录占用排名；二是排序规则错误，没有按年份、月份的先后顺序升序排列。以下提供两种可直接运行的实现：

方案1：窗口函数实现（推荐，适配MySQL 8.0+、PostgreSQL、SQL Server等主流支持窗口函数的引擎） #

先筛选所有价值为0的记录，再按马匹分组，按年份升序、月份升序排序，取每组排名为1的记录即可：

SELECT rowID, horseID, valueYear, valueMonth, value
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY horseID 
            ORDER BY valueYear ASC, valueMonth ASC
        ) AS rn
    FROM 你的实际表名 -- 替换为真实表名即可
    WHERE value = 0
) t
WHERE rn = 1;

注意：不要随意用RANK()替换ROW_NUMBER()，如果同一匹马存在同年月多条value=0的记录，RANK()会返回多条结果，ROW_NUMBER()可保证每组仅取最早的一条，若业务允许同年月多条同取可替换为RANK()。

方案2：关联聚合查询实现（兼容不支持窗口函数的老版本SQL环境） #

先通过聚合查询找到每匹马价值为0的最早年月，再关联原表匹配对应记录：

SELECT t1.*
FROM 你的实际表名 t1 -- 替换为真实表名即可
INNER JOIN (
    SELECT 
        horseID,
        MIN(valueYear * 100 + valueMonth) AS earliest_ym
    FROM 你的实际表名
    WHERE value = 0
    GROUP BY horseID
) t2 
ON t1.horseID = t2.horseID 
AND t1.valueYear * 100 + t1.valueMonth = t2.earliest_ym
AND t1.value = 0;

两种方案执行后均会返回rowID=8和rowID=20的两条目标记录，符合需求。

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