SQL需求：当前行flag为True时获取该行及后续两行（仅首个True）

需求说明 #

按ID分组、每组内按price排序后，找到每组中第一个flag为True的行，获取该行及其后续最多两行（后续行不足时补NULL）。

示例输入表 #

预期输出 #

通用SQL实现（兼容PostgreSQL/MySQL8.0+/SQL Server） #

WITH ranked_data AS (
    SELECT 
        ID,
        price,
        flag,
        -- 每组内按price升序生成行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY price) AS rn,
        -- 标记每组第一个flag为true的行的行号
        MIN(CASE WHEN flag = 'true' THEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY price) END) OVER (PARTITION BY ID) AS first_true_rn
    FROM your_table
),
-- 为每个ID生成3行需求（对应目标的3条记录）
required_rows AS (
    SELECT ID, generate_series(1,3) AS target_rn
    FROM (SELECT DISTINCT ID FROM your_table) AS ids
)
SELECT 
    rr.ID,
    -- 示例中A列为随机值，可替换为实际业务逻辑
    CASE 
        WHEN rd.rn = rd.first_true_rn THEN 456
        WHEN rd.rn = rd.first_true_rn +1 THEN 786
        WHEN rd.rn = rd.first_true_rn +2 THEN 567
        ELSE NULL 
    END AS A,
    rd.price,
    rd.flag
FROM required_rows rr
LEFT JOIN (
    SELECT * FROM ranked_data
    WHERE rn BETWEEN first_true_rn AND first_true_rn +2
) rd ON rr.ID = rd.ID AND (rd.rn - rd.first_true_rn +1) = rr.target_rn
ORDER BY rr.ID, rr.target_rn;

MySQL兼容版（替换generate_series） #

WITH ranked_data AS (
    SELECT 
        ID,
        price,
        flag,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY price) AS rn,
        MIN(CASE WHEN flag = 'true' THEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY price) END) OVER (PARTITION BY ID) AS first_true_rn
    FROM your_table
),
required_rows AS (
    SELECT ID, 1 AS target_rn FROM (SELECT DISTINCT ID FROM your_table) AS ids
    UNION ALL
    SELECT ID, 2 AS target_rn FROM (SELECT DISTINCT ID FROM your_table) AS ids
    UNION ALL
    SELECT ID, 3 AS target_rn FROM (SELECT DISTINCT ID FROM your_table) AS ids
)
SELECT 
    rr.ID,
    CASE 
        WHEN rd.rn = rd.first_true_rn THEN 456
        WHEN rd.rn = rd.first_true_rn +1 THEN 786
        WHEN rd.rn = rd.first_true_rn +2 THEN 567
        ELSE NULL 
    END AS A,
    rd.price,
    rd.flag
FROM required_rows rr
LEFT JOIN (
    SELECT * FROM ranked_data
    WHERE rn BETWEEN first_true_rn AND first_true_rn +2
) rd ON rr.ID = rd.ID AND (rd.rn - rd.first_true_rn +1) = rr.target_rn
ORDER BY rr.ID, rr.target_rn;

逻辑说明 #

1. ranked_data：为每组内的行按price排序编号，同时计算每组第一个flag=true行的编号。
2. required_rows：为每个ID生成3条行记录，确保最终输出每组固定3行。
3. 左连接筛选：将目标行与生成的3行需求关联，不足的行自动补NULL，保证输出格式统一。

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