SQL查询指定学生中未关联全部选中分组的实现方法

核心逻辑为：统计每个选中学生关联的指定分组数量，和指定分组的总数量做对比，只要关联数量小于总数量，即为未关联全部指定分组的目标学生。

通用SQL实现 #

-- 替换下面IN条件里的学生ID、分组ID即可适配不同查询需求
SELECT 
  s.Id, 
  s.Name 
FROM Student s
WHERE 
  -- 过滤选中的学生列表
  s.Id IN ('02','03')
  AND (
    -- 统计当前学生关联的指定分组数量
    SELECT COUNT(DISTINCT sg.Group_Id)
    FROM StudentGroup sg
    WHERE 
      sg.Student_id = s.Id
      AND sg.Group_Id IN ('12','13')
  ) < (
    -- 统计指定分组的总数量
    SELECT COUNT(DISTINCT g.Id)
    FROM `Group` g
    WHERE g.Id IN ('12','13')
  );

注意：Group为SQL保留字，查询时需要用反引号转义，避免语法报错。

效果验证 #

样例1适配 #

替换参数为学生ID('02','03')、分组ID('12','13')，查询结果为StudentB、StudentC，和预期一致。

样例2适配 #

替换参数为学生ID('01','03')、分组ID('11','13')，如果需要输出StudentC(03)格式的结果，调整SELECT部分即可：

SELECT CONCAT(s.Name, '(', s.Id, ')') AS student_info
FROM Student s
WHERE 
  s.Id IN ('01','03')
  AND (
    SELECT COUNT(DISTINCT sg.Group_Id)
    FROM StudentGroup sg
    WHERE 
      sg.Student_id = s.Id
      AND sg.Group_Id IN ('11','13')
  ) < (
    SELECT COUNT(DISTINCT g.Id)
    FROM `Group` g
    WHERE g.Id IN ('11','13')
  );

查询结果为StudentC(03)，和预期一致。

原有方案问题说明 #

• 尝试的实现方法1：子查询返回的是所有和任意指定分组有关联的学生ID，NOT IN仅过滤完全没有关联任何指定分组的学生，无法筛选「关联了部分但没关联全」的学生。
• 尝试的实现方法2：窗口函数的写法没有预先构造「选中学生+选中分组」的笛卡尔积基准表，导致未关联的分组无法和学生正确匹配，NULL值统计逻辑失效。

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