如何编写SQL查询获取有一门及以上科目不及格学生的所有记录

问题背景 #

需要编写SELECT语句，获取有一门或多门科目不及格学生的全部记录。现有student表，字段包括Student_id、name、subject、marks。

现有表数据 #

期望输出 #

返回所有存在不及格科目（marks < 50）的学生的全部科目记录，结果如下：

错误尝试的SQL #

用户尝试了以下查询，但无法得到有效结果：

select count(*) 
from (select student_id 
      from student 
      where marks < 50 
      group by student_id 
      having count(subject) > 1) as dt

问题分析与正确解法 #

问题分析 #

原查询存在两个核心问题：

• 仅筛选了有两门及以上不及格科目的学生，不符合"一门或多门"的需求
• 最终输出的是符合条件的学生数量，而非学生的全部记录

正确SQL写法 #

以下几种方法均可实现需求：

方法1：子查询筛选学生ID

SELECT *
FROM student
WHERE Student_id IN (
    SELECT DISTINCT Student_id
    FROM student
    WHERE marks < 50
);

方法2：EXISTS关联查询

SELECT s1.*
FROM student s1
WHERE EXISTS (
    SELECT 1
    FROM student s2
    WHERE s2.Student_id = s1.Student_id
      AND s2.marks < 50
);

方法3：窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库）

SELECT Student_id, name, subject, marks
FROM (
    SELECT *,
           MAX(CASE WHEN marks < 50 THEN 1 ELSE 0 END) OVER (PARTITION BY Student_id) AS has_fail
    FROM student
) t
WHERE has_fail = 1;

以上方法都会返回所有存在不及格科目的学生的全部记录，完全匹配需求。

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