按学生分组的SQL FLAG计算需求（含数据样本与规则）

给定如下表结构和数据：

CREATE TABLE t
    (STUDENT int, SCORE int, DATE date)
;
    
INSERT INTO t
    (STUDENT, SCORE, DATE)
VALUES
    (1, 6, '2022-02-01 00:00:00'),
    (1, 2, '2022-03-12 00:00:00'),
    (1, 5, '2022-04-30 00:00:00'),
    (2, 2, '2022-04-12 00:00:00'),
    (2, 0, '2022-04-17 00:00:00'),
    (2, 7, '2022-05-08 00:00:00'),
    (3, 2, '2022-03-16 00:00:00'),
    (3, 6, '2022-03-18 00:00:00'),
    (3, 2, '2022-04-02 00:00:00'),
    (3, 9, '2022-04-27 00:00:00'),
    (4, 4, '2022-02-24 00:00:00'),
    (4, 0, '2022-02-26 00:00:00'),
    (5, 3, '2022-01-28 00:00:00'),
    (5, 0, '2022-02-21 00:00:00'),
    (5, 4, '2022-04-05 00:00:00')
;

需要生成包含STUDENT和FLAG字段的查询结果，FLAG计算规则如下：

• 分别找出每个学生SCORE=2时的最小日期（记为min_date_2）和SCORE=6时的最小日期（记为min_date_6）
• 若min_date_2 < min_date_6，则FLAG=1
• 若min_date_2 >= min_date_6，则FLAG=0
• 若学生有SCORE=2的记录但无SCORE=6的记录，则FLAG=1

通过条件聚合获取每个学生的目标最小日期，再用CASE语句匹配规则生成FLAG：

SELECT
    STUDENT,
    CASE
        WHEN min_date_2 IS NOT NULL THEN
            CASE
                WHEN min_date_6 IS NULL OR min_date_2 < min_date_6 THEN 1
                ELSE 0
            END
    END AS FLAG
FROM (
    SELECT
        STUDENT,
        MIN(CASE WHEN SCORE = 2 THEN DATE END) AS min_date_2,
        MIN(CASE WHEN SCORE = 6 THEN DATE END) AS min_date_6
    FROM t
    GROUP BY STUDENT
) AS sub
WHERE min_date_2 IS NOT NULL;

执行上述SQL后得到如下结果：

• 子查询通过CASE分组聚合，分别提取每个学生SCORE=2和6的最小日期
• 外层CASE先筛选出有SCORE=2记录的学生，再按照规则判断FLAG值
• 若需包含所有学生（即使无SCORE=2记录），可去掉外层的WHERE条件，此类学生的FLAG会显示为NULL

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