SQL同ID多行不同subject的CASE条件判断问题求助

你的原查询问题在于CASE条件是在单行内判断subject同时等于两个不同值，但单行的subject只能有一个值，所以这些条件永远不会成立。要实现按Studentid判断科目组合，需要先统计每个学生的科目集合，再结合当前行的subject进行标记。

修正后的SQL（窗口函数版） #

SELECT 
    Studentid,
    subject,
    date_added,
    CASE
        -- 学生同时拥有maths和PCM，且当前科目是二者之一
        WHEN (COUNT(CASE WHEN subject = 'maths' THEN 1 END) OVER (PARTITION BY Studentid) >= 1
              AND COUNT(CASE WHEN subject = 'PCM' THEN 1 END) OVER (PARTITION BY Studentid) >= 1)
             AND subject IN ('maths', 'PCM') THEN 'Science_student'
        -- 学生同时拥有biology和arts，且当前科目是二者之一
        WHEN (COUNT(CASE WHEN subject = 'biology' THEN 1 END) OVER (PARTITION BY Studentid) >= 1
              AND COUNT(CASE WHEN subject = 'arts' THEN 1 END) OVER (PARTITION BY Studentid) >= 1)
             AND subject IN ('biology', 'arts') THEN 'Non-Pcm'
        ELSE NULL
    END AS Student_type
FROM students
ORDER BY Studentid, date_added DESC;

逻辑说明 #

1. 窗口函数分组统计：通过PARTITION BY Studentid将数据按学生分组，用COUNT统计每个学生是否拥有指定科目；
2. 双重条件判断：
   • 先判断学生是否同时满足科目组合（比如maths+PCM）；
   • 再判断当前行的subject是否属于该组合，确保只有对应科目行才标记类型；
3. 其他情况返回NULL：不满足任何组合或当前科目不属于组合时，返回NULL。

验证结果 #

执行上述SQL后，会得到你预期的输出：

Studentid subject     date_added Student_type
12184      maths      15mar2024   Science_student
12184      arts       15mar2024   null
12184      PCM        14mar2024   Science_student  
12185      accounts   11aug2024   null   
12185      arts       10aug2024   null
12198      biology    17aug2024   Non-Pcm
12198      arts       16aug2024   Non-Pcm

备选方案（子查询聚合版） #

如果你的数据库不支持窗口函数，可以用子查询先聚合学生类型，再关联回原表：

WITH student_subject_check AS (
    SELECT 
        Studentid,
        -- 标记学生是否满足Science_student条件
        MAX(CASE WHEN subject = 'maths' THEN 1 ELSE 0 END) AS has_maths,
        MAX(CASE WHEN subject = 'PCM' THEN 1 ELSE 0 END) AS has_pcm,
        -- 标记学生是否满足Non-Pcm条件
        MAX(CASE WHEN subject = 'biology' THEN 1 ELSE 0 END) AS has_biology,
        MAX(CASE WHEN subject = 'arts' THEN 1 ELSE 0 END) AS has_arts
    FROM students
    GROUP BY Studentid
)
SELECT 
    s.Studentid,
    s.subject,
    s.date_added,
    CASE
        WHEN ssc.has_maths = 1 AND ssc.has_pcm = 1 AND s.subject IN ('maths', 'PCM') THEN 'Science_student'
        WHEN ssc.has_biology = 1 AND ssc.has_arts = 1 AND s.subject IN ('biology', 'arts') THEN 'Non-Pcm'
        ELSE NULL
    END AS Student_type
FROM students s
LEFT JOIN student_subject_check ssc ON s.Studentid = ssc.Studentid
ORDER BY s.Studentid, s.date_added DESC;

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