编写MySQL查询：筛选通过测试1-3且未通过测试4的学生（含重考场景）

这个问题的核心是要以学生每门测试的最新状态为准，毕竟存在重考后状态变更的情况（比如示例里student_id=2的测试1先failed后passed）。下面是两种可行的MySQL查询方案：

方案一：CTE+分组筛选（推荐） #

WITH latest_test_results AS (
    SELECT 
        test_id,
        student_id,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, test_id 
            ORDER BY completed_on DESC
        ) AS rn
    FROM student_tests
)
SELECT DISTINCT student_id
FROM latest_test_results
WHERE rn = 1
GROUP BY student_id
HAVING 
    -- 验证测试1-3的最终状态为passed
    MAX(CASE WHEN test_id = 1 THEN status END) = 'passed'
    AND MAX(CASE WHEN test_id = 2 THEN status END) = 'passed'
    AND MAX(CASE WHEN test_id = 3 THEN status END) = 'passed'
    -- 验证测试4的最终状态为failed
    AND MAX(CASE WHEN test_id = 4 THEN status END) = 'failed';

逻辑拆解： #

1. CTE latest_test_results：用ROW_NUMBER()窗口函数给每个学生的每门测试记录按完成时间降序编号，最新的记录会被标记为rn=1，这样我们就精准拿到了每个学生每门测试的最终状态。
2. 外层查询：先筛选出所有最新测试记录（rn=1），再按学生分组，通过HAVING子句逐一检查条件：
   • 测试1、2、3的最新状态必须都是passed
   • 测试4的最新状态必须是failed
     这里用MAX()是因为每个测试在分组后只有一条最新记录，所以MAX()就等同于该测试的最终状态。

方案二：交集筛选（逻辑更直观） #

如果你觉得分组筛选的逻辑不够清晰，也可以分别找出符合两个核心条件的学生，再取交集：

WITH latest_test_results AS (
    SELECT 
        test_id,
        student_id,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, test_id 
            ORDER BY completed_on DESC
        ) AS rn
    FROM student_tests
)
-- 先找出通过了测试1-3的学生（确保三个测试都完成且通过）
SELECT student_id
FROM latest_test_results
WHERE rn = 1
AND (test_id IN (1,2,3) AND status='passed')
GROUP BY student_id
HAVING COUNT(DISTINCT test_id) = 3
INTERSECT
-- 再找出测试4未通过的学生
SELECT student_id
FROM latest_test_results
WHERE rn = 1
AND test_id=4 AND status='failed';

兼容低版本MySQL（无CTE支持） #

如果你的MySQL版本低于8.0，不支持CTE语法，可以把CTE替换成子查询：

SELECT DISTINCT student_id
FROM (
    SELECT 
        test_id,
        student_id,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, test_id 
            ORDER BY completed_on DESC
        ) AS rn
    FROM student_tests
) AS latest_test_results
WHERE rn = 1
GROUP BY student_id
HAVING 
    MAX(CASE WHEN test_id = 1 THEN status END) = 'passed'
    AND MAX(CASE WHEN test_id = 2 THEN status END) = 'passed'
    AND MAX(CASE WHEN test_id = 3 THEN status END) = 'passed'
    AND MAX(CASE WHEN test_id = 4 THEN status END) = 'failed';

内容的提问来源于stack exchange，提问作者Damon Daisley Wilson