如何使用SQL查询至少共同选修2门课程的"学生好友"关系

现有基础数据表说明 #

目前已经搭建完成三类核心基础表，支撑本次查询需求：

• 学生信息表：存储学生维度的基础档案信息，核心关联字段为student_id（学生唯一标识）
• 课程信息表：存储课程维度的基础档案信息，核心关联字段为course_id（课程唯一标识）
• 学生选课关联表：存储学生与课程的多对多选课映射关系，每条记录对应一名学生的一门选课明细，核心字段为student_id、course_id

需求判定规则 #

本次需要筛选的“学生好友”配对，判定标准为：两名学生至少共同选修2门完全相同的课程。

核心查询逻辑 #

实现该需求不需要复杂的跨表关联，基于选课关联表自连接即可完成统计，核心思路为：

1. 对选课关联表做自连接，连接条件为两条选课记录对应的course_id完全相等（即匹配两个学生共同选了同一门课的场景）
2. 增加过滤条件避免无效、重复配对：设置两个学生的student_id满足stu1.student_id < stu2.student_id，从根源上排除自己和自己配对、(A,B)和(B,A)被重复统计为两组结果的问题
3. 按两个学生的配对维度分组，统计每组配对下匹配到的共同课程总数
4. 筛选出共同课程数大于等于2的配对，即为符合要求的好友关系

参考查询SQL #

SELECT
  s1.student_id AS first_student_id,
  s2.student_id AS second_student_id,
  COUNT(DISTINCT s1.course_id) AS shared_course_total
FROM student_course s1
INNER JOIN student_course s2
  ON s1.course_id = s2.course_id
  AND s1.student_id < s2.student_id
GROUP BY s1.student_id, s2.student_id
HAVING COUNT(DISTINCT s1.course_id) >= 2;

实现注意事项 #

• 统计共同课程数时使用DISTINCT去重，避免选课表存在重复录入的冗余记录时，出现统计值虚高的问题
• 如果需要展示学生姓名、所选共同课程的明细信息，可以在上述查询结果的基础上，关联学生信息表、课程信息表补全对应字段即可

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