如何编写SQL查询筛选包含Student_1全部lesson_id的学生配对行

我有一张包含Student_1、Student_2、lesson_id字段的数据库表，原始数据如下：

需要编写SQL查询，返回满足以下条件的数据行：每一组(Student_1, Student_2)配对中，包含该Student_1所拥有的全部lesson_id。

期望返回结果如下：

举例说明：配对(352-03-3624, 805-17-4144)符合条件，因为它包含了Student_1（352-03-3624）的所有lesson_id（27、49、50）；而配对(352-03-3624, 805-17-4143)不符合，因为缺少lesson_id 49和50。

可以通过以下SQL语句实现需求：

WITH student_lessons AS (
    -- 统计每个Student_1拥有的唯一lesson_id总数
    SELECT Student_1, COUNT(DISTINCT lesson_id) AS total_lessons
    FROM your_table_name
    GROUP BY Student_1
),
pair_lessons AS (
    -- 统计每个(Student_1, Student_2)配对包含的唯一lesson_id数量
    SELECT Student_1, Student_2, COUNT(DISTINCT lesson_id) AS pair_lesson_count
    FROM your_table_name
    GROUP BY Student_1, Student_2
)
-- 筛选出配对课程数等于Student_1总课程数的记录，关联原表获取完整行数据
SELECT t.*
FROM your_table_name t
JOIN pair_lessons pl ON t.Student_1 = pl.Student_1 AND t.Student_2 = pl.Student_2
JOIN student_lessons sl ON t.Student_1 = sl.Student_1
WHERE pl.pair_lesson_count = sl.total_lessons
ORDER BY t.Student_1, t.lesson_id;

思路说明 #

1. CTE student_lessons：先计算每个Student_1的唯一课程总数，作为判断的基准值。
2. CTE pair_lessons：计算每一组学生配对下，实际覆盖的课程数量。
3. 关联筛选：将原表与两个CTE关联，筛选出配对课程数等于该Student_1总课程数的所有行，确保该配对包含了该学生的全部课程。

注意：将语句中的your_table_name替换为你实际的表名。

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