如何对学生GPA数据按学生学期排序后标记连续两次GPA<2.5的行

你可以通过窗口函数完成连续序列识别，配合CTE复用你已有的字段计算逻辑，无需重复写CONCAT/IIF/CASE的计算代码，完整实现逻辑如下：

WITH base_data AS (
    -- 此处直接替换为你现有的查询逻辑，已经生成Student、Term、GPA Category三个字段即可
    SELECT
        CONCAT(first_name, ' ', last_name) AS Student, -- 你原有的Student计算逻辑
        term_name AS Term, -- 你原有的Term字段
        CASE WHEN gpa >= 2.5 THEN 'GPA OK' ELSE 'GPA < 2.5' END AS `GPA Category` -- 你原有的GPA分类逻辑
    FROM your_original_table -- 替换为你的原始业务表名
),
-- 处理学期排序逻辑，避免字符串格式的学期排序错误
term_ordered_data AS (
    SELECT 
        *,
        -- 把字符串学期转换为可排序的数字，规则：同一年份内 Spring < Summer < Fall，可根据你实际的学期命名规则调整
        CAST(SUBSTRING_INDEX(Term, ' ', -1) AS UNSIGNED) * 10 + 
        CASE SUBSTRING_INDEX(Term, ' ', 1)
            WHEN 'Spring' THEN 1
            WHEN 'Summer' THEN 2
            WHEN 'Fall' THEN 3
        END AS term_order
    FROM base_data
),
-- 标记每个学生的GPA分类是否为新连续序列的起点
seq_marker AS (
    SELECT 
        *,
        CASE 
            WHEN `GPA Category` = 'GPA < 2.5' 
             AND LAG(`GPA Category`, 1, 'GPA OK') OVER (PARTITION BY Student ORDER BY term_order) = 'GPA < 2.5'
            THEN 0 -- 当前行属于上一个<2.5的连续序列
            ELSE 1 -- 当前行是新序列起点（要么是GPA OK，要么是第一次出现<2.5）
        END AS is_new_seq
    FROM term_ordered_data
),
-- 给每个连续序列分配唯一组号
seq_group AS (
    SELECT 
        *,
        SUM(is_new_seq) OVER (PARTITION BY Student ORDER BY term_order) AS group_id
    FROM seq_marker
),
-- 计算每个连续组内的行序号
rank_in_group AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Student, group_id ORDER BY term_order) AS group_row_num
    FROM seq_group
)
-- 最终生成FLAG字段
SELECT 
    Student,
    Term,
    `GPA Category`,
    CASE 
        WHEN `GPA Category` = 'GPA OK' THEN 0
        WHEN group_row_num = 1 THEN 1
        ELSE 2
    END AS FLAG
FROM rank_in_group
ORDER BY Student, term_order;

注意：代码中带空格的字段名GPA Category用了MySQL的反引号包裹，如果你使用SQL Server请替换为[GPA Category]，使用Oracle请替换为"GPA Category"。如果你的数据库版本不支持CTE语法，把每层逻辑改为子查询嵌套即可，计算逻辑完全一致，窗口函数兼容MySQL 8.0+/PostgreSQL 9.4+/SQL Server 2012+/Oracle 11g+所有主流关系型数据库。

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