无需递归CTE：用JOINS、ANY/IN查询与Sally的一至三度关联学生

数据库表结构 #

course表 #

course_id       name
    3             Physics
   12             English
   19             Basket Weaving
    4             Computer Science
  212             Discrete Math
  102             Biology
   20             Chemistry
   50             Robotics
    7             Data Engineering

student表 #

id    name
 2    Sally
 1    Bob
17    Robert
 9    Pierre
12    Sydney
41    James
22    William
 5    Mary
 3    Robert
92    Doris
 6    Harry

students_in表 #

course_id   student_id      grade
    3              2              B
  212              2              A
    3             12              A
   19             12              C
    3             41              A
    4             41              B
  212             41              F
   19             41              A
   12             41              B
    3             17              C
    4              1              A
  102              1              D
  102             22              A
   20             22              A
   20              5              B
   50              3              A
   12             92              B
   12             17              C
    7              6              A

需求 #

获取满足以下任一条件的学生ID和姓名（排除Sally本人）：

• 与Sally选过同一门课（一度关联）；
• 与上述一度关联的学生选过同一门课（二度关联）；
• 与上述二度关联的学生选过同一门课（三度关联）。

问题 #

除了递归CTE，能否通过更简洁的方式实现？比如：

• 使用JOIN和子查询；
• 使用ANY或IN运算符？

解决方案 #

方法1：多层JOIN + EXISTS #

通过关联选课表的层级关系直接匹配符合条件的学生，最终去重：

SELECT DISTINCT s.id, s.name
FROM student s
JOIN students_in si ON s.id = si.student_id
WHERE s.id != 2 -- 排除Sally本人
AND EXISTS (
    SELECT 1
    FROM students_in si_sally -- Sally的选课记录
    LEFT JOIN students_in si1 ON si_sally.course_id = si1.course_id -- 关联一度学生的选课
    LEFT JOIN students_in si2 ON si1.course_id = si2.course_id -- 关联二度学生的选课
    WHERE si_sally.student_id = 2
    AND (
        si.course_id = si_sally.course_id -- 匹配一度关联
        OR si.course_id = si1.course_id -- 匹配二度关联
        OR si.course_id = si2.course_id -- 匹配三度关联
    )
);

方法2：IN运算符 + 层级子查询 #

通过嵌套子查询分别获取各层级关联的学生ID集合，再统一筛选：

SELECT id, name
FROM student
WHERE id != 2 -- 排除Sally本人
AND id IN (
    -- 一度关联学生：与Sally同课的学生
    SELECT DISTINCT si1.student_id
    FROM students_in si1
    WHERE si1.course_id IN (SELECT course_id FROM students_in WHERE student_id = 2)
    
    UNION
    
    -- 二度关联学生：与一度关联学生同课的学生
    SELECT DISTINCT si2.student_id
    FROM students_in si2
    WHERE si2.course_id IN (
        SELECT course_id FROM students_in 
        WHERE student_id IN (
            SELECT DISTINCT si1.student_id 
            FROM students_in si1 
            WHERE si1.course_id IN (SELECT course_id FROM students_in WHERE student_id = 2)
        )
    )
    
    UNION
    
    -- 三度关联学生：与二度关联学生同课的学生
    SELECT DISTINCT si3.student_id
    FROM students_in si3
    WHERE si3.course_id IN (
        SELECT course_id FROM students_in 
        WHERE student_id IN (
            SELECT DISTINCT si2.student_id 
            FROM students_in si2 
            WHERE si2.course_id IN (
                SELECT course_id FROM students_in 
                WHERE student_id IN (
                    SELECT DISTINCT si1.student_id 
                    FROM students_in si1 
                    WHERE si1.course_id IN (SELECT course_id FROM students_in WHERE student_id = 2)
                )
            )
        )
    )
);

说明 #

两种方法均可实现需求：JOIN+EXISTS的方式性能更优，避免了多层嵌套子查询的重复计算；IN+UNION的方式逻辑直观，便于理解层级关联关系。

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