如何编写SQL选取每类id首次rating=10前及该行的所有数据

以下几种写法都可以实现需求，你可以根据自己使用的数据库版本选择：

方案1：CTE关联写法（支持MySQL 8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

先提取每个出现过rating=10的id的首次达标日期，再关联原表筛选日期早于等于该日期的记录：

WITH first_10_date AS (
    SELECT 
        id,
        MIN(day) AS first_day
    FROM results
    WHERE rating = 10
    GROUP BY id
)
SELECT r.id, r.rating, r.day
FROM results r
INNER JOIN first_10_date f 
    ON r.id = f.id 
    AND r.day <= f.first_day
ORDER BY r.id, r.day;

方案2：窗口函数写法 #

单次表扫描即可完成计算，性能更优：

SELECT id, rating, day
FROM (
    SELECT 
        *,
        MIN(CASE WHEN rating = 10 THEN day END) OVER (PARTITION BY id) AS first_10_day
    FROM results
) temp
WHERE first_10_day IS NOT NULL 
  AND day <= first_10_day
ORDER BY id, day;

方案3：兼容低版本数据库的关联子查询写法 #

适合不支持CTE、窗口函数的老版本数据库：

SELECT r.id, r.rating, r.day
FROM results r
WHERE 
    -- 过滤从未出现过rating=10的id
    EXISTS (
        SELECT 1 FROM results r2 
        WHERE r2.id = r.id AND r2.rating = 10
    )
    -- 保留首次拿到10分当天及之前的记录
    AND r.day <= (
        SELECT MIN(r3.day) FROM results r3 
        WHERE r3.id = r.id AND r3.rating = 10
    )
ORDER BY r.id, r.day;

以上写法均符合预期结果：会自动过滤从来没有出现过rating=10的id（比如示例中的id3、id4），同时保留每个id首次拿到10分当天及之前的所有行。

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