如何编写SQL统计首次通过两项评估的课程数量？

数据库表结构（表名：tbl） #

需求说明 #

编写SQL查询，统计那些两项评估（test1和test2）均首次尝试就通过的课程数量（同一评估可多次参加）。

示例说明：上述数据的查询结果应为1——课程000的test1首次尝试（第1行）通过，test2首次尝试（第5行）也通过；课程001虽test1首次通过，但test2首次尝试未通过，不计入统计。

已有查询代码 #

SELECT course_id, eval_type, min(date)
FROM tbl
GROUP BY course_id, eval_type

注：该代码存在字段名错误，实际应使用eval_date而非date。

完整解决方案 #

思路分解 #

1. 获取每门课程每项评估的首次尝试日期；
2. 关联原表筛选出首次尝试就通过的记录；
3. 统计同时满足test1和test2首次通过的课程数量。

最终SQL代码 #

-- 1. 获取每门课程每项评估的首次尝试日期
WITH first_attempts AS (
    SELECT course_id, eval_type, MIN(eval_date) AS first_eval_date
    FROM tbl
    GROUP BY course_id, eval_type
),
-- 2. 筛选出首次尝试通过的课程+评估组合
passed_first_attempts AS (
    SELECT fa.course_id, fa.eval_type
    FROM first_attempts fa
    JOIN tbl t 
        ON fa.course_id = t.course_id 
        AND fa.eval_type = t.eval_type 
        AND fa.first_eval_date = t.eval_date
    WHERE t.`Passed?` = 'Y'
)
-- 3. 统计同时拥有test1和test2首次通过的课程数
SELECT COUNT(DISTINCT course_id) AS qualified_course_count
FROM (
    SELECT course_id
    FROM passed_first_attempts
    WHERE eval_type IN ('test1', 'test2')
    GROUP BY course_id
    HAVING COUNT(DISTINCT eval_type) = 2
) AS qualified_courses;

代码说明 #

• first_attempts CTE：修正了已有代码的字段错误，通过分组获取每门课程每项评估的最早尝试日期；
• passed_first_attempts CTE：将首次尝试日期与原表关联，筛选出该次尝试通过的记录；
• 最后一层查询：仅保留包含test1和test2的记录，按课程分组后，只有当两个评估都满足首次通过的课程才会被纳入统计，最终得到符合条件的课程总数。

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