MySQL查询：筛选首行C2为A且末行C3为D的C1值

我在MySQL中有一张名为my_table的数据表，结构和数据如下：

需要筛选出满足以下条件的C1值：同一C1分组内，按R排序后的首行C2为A，且末行C3为D。

注：数据行按R字段排序，比如C1=1的分组里，首行C2是A，末行C3是D，符合条件。

预期输出：

分组情况说明：

• C1=1：3行数据，首行C2为A，末行C3为D，符合条件
• C1=2：仅1行数据，C2为A同时C3为D，符合条件
• C1=3：2行数据，首行C2为A，但末行C3为X，不符合条件

方法1：用行号标记定位首末行 #

通过窗口函数ROW_NUMBER()为每个C1分组内的行按R排序编号，同时统计分组总行数，再筛选符合首末行条件的分组：

WITH ranked_rows AS (
    SELECT 
        C1,
        C2,
        C3,
        ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY R) AS row_num,
        COUNT(*) OVER (PARTITION BY C1) AS total_rows
    FROM my_table
)
SELECT DISTINCT C1
FROM ranked_rows
WHERE 
    row_num = 1 AND C2 = 'A'
    AND EXISTS (
        SELECT 1 
        FROM ranked_rows rr
        WHERE rr.C1 = ranked_rows.C1 
          AND rr.row_num = rr.total_rows 
          AND rr.C3 = 'D'
    );

方法2：直接提取首末行字段值 #

利用FIRST_VALUE()和LAST_VALUE()窗口函数，直接获取每个C1分组的首行C2和末行C3，再筛选符合条件的分组：

SELECT C1
FROM (
    SELECT 
        C1,
        FIRST_VALUE(C2) OVER (PARTITION BY C1 ORDER BY R) AS first_c2,
        LAST_VALUE(C3) OVER (PARTITION BY C1 ORDER BY R ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_c3
    FROM my_table
) t
WHERE first_c2 = 'A' AND last_c3 = 'D'
GROUP BY C1;

执行上述任意一段SQL，都会得到预期的输出：1和2两个C1值，完全符合需求。

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