MySQL 8.0.21同表多列子查询失效及符合条件学生统计需求

需求说明 #

按dt_reference（日期）、class_id分组，统计满足以下条件的去重student_id数量：

• 该student_id在对应日期和班级下的所有grade均为0
• 该student_id在对应日期和班级下的所有active值均为TRUE

建表与测试数据 #

create table students (
    dt_reference DATE, 
    CLASS_ID number(10), 
    STUDENT_ID number(10), 
    GRADE number(10), 
    ACTIVE boolean
);

insert into students values ('2022-10-10', 1, 100, 10,TRUE);
insert into students values ('2022-10-10', 1, 100, 0,TRUE);
insert into students values ('2022-10-10', 1, 100, 0,TRUE);
insert into students values ('2022-10-11', 1, 100, 0,TRUE);
insert into students values ('2022-10-11', 1, 100, 0,TRUE);
insert into students values ('2022-10-10', 2, 101, 5,TRUE);
insert into students values ('2022-10-10', 2, 101, 2,TRUE);
insert into students values ('2022-10-12', 2, 102, 0,FALSE);
insert into students values ('2022-10-12', 2, 102, 0,FALSE);
insert into students values ('2022-10-13', 3, 103, 0,TRUE);
insert into students values ('2022-10-13', 3, 100, 0,TRUE);
insert into students values ('2022-10-13', 3, 100, 5,TRUE);

错误查询语句分析 #

你提供的查询存在两个核心问题：

1. 逻辑完全偏差：使用NOT EXISTS查找不存在grade=0且active=TRUE的记录，与需求完全相悖
2. 语法不完整：缺少NOT EXISTS子查询的闭合括号

原错误语句：

SELECT STUDENT_ID, CLASS_ID, DATE, GRADE, ACTIVE
FROM students X
WHERE NOT EXISTS (
 SELECT 1
 from students Y
 WHERE Y.STUDENT_ID = X.STUDENT_ID
    and Y.CLASS_ID = X.CLASS_ID
    and Y.DATE = X.DATE
    and Y.GRADE = 0
    and Y.ACTIVE = TRUE
-- 缺少闭合的 )

正确解决方案 #

方法1：双层分组筛选 #

先按日期、班级、学生分组，筛选出符合条件的学生，再按日期、班级统计数量：

SELECT 
    dt_reference,
    class_id,
    COUNT(DISTINCT student_id) AS qualified_student_count
FROM students
GROUP BY dt_reference, class_id, student_id
HAVING MAX(grade) = 0 AND MIN(active) = TRUE
GROUP BY dt_reference, class_id;

• MAX(grade) = 0：确保该学生在当天班级的所有grade都是0（若存在非0值，MAX会大于0）
• MIN(active) = TRUE：确保该学生在当天班级的所有active都是TRUE（若存在FALSE，MIN会是FALSE）

方法2：子查询+条件统计 #

用CASE语句统计不符合条件的记录数，筛选出无不符合记录的学生后再统计：

SELECT 
    dt_reference,
    class_id,
    COUNT(DISTINCT student_id) AS qualified_student_count
FROM (
    SELECT 
        dt_reference,
        class_id,
        student_id
    FROM students
    GROUP BY dt_reference, class_id, student_id
    HAVING SUM(CASE WHEN grade != 0 THEN 1 ELSE 0 END) = 0 
        AND SUM(CASE WHEN active != TRUE THEN 1 ELSE 0 END) = 0
) AS qualified_students
GROUP BY dt_reference, class_id;

• SUM(CASE WHEN grade !=0 THEN 1 ELSE 0 END) =0：表示没有非0的grade记录
• SUM(CASE WHEN active !=TRUE THEN 1 ELSE 0 END) =0：表示没有非TRUE的active记录

预期查询结果 #

执行上述正确语句后，会得到以下结果：

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