MySQL自连接count unique统计常见双专业组合SQL问题

原有写法的核心问题 #

之前的SQL存在3个明显逻辑漏洞：

• 自连接未绑定相同StudentID，会把不同学生的专业错误配对
• 仅用A.Major <> B.Major判断不等，会把(Science,Maths)和(Maths,Science)识别为两个不同组合，导致重复统计
• 计数逻辑用关联子查询实现，没有按组合做分组聚合，效率低且结果不准

正确SQL代码 #

SELECT 
  A.Major AS Major1,
  B.Major AS Major2,
  COUNT(DISTINCT A.StudentID) AS Count_of_Students
FROM Table1 A
INNER JOIN Table1 B 
  ON A.StudentID = B.StudentID 
  AND A.Major < B.Major
GROUP BY Major1, Major2
HAVING Count_of_Students >= 2
ORDER BY Count_of_Students DESC, Major1, Major2;

关键逻辑说明 #

• 自连接时强制A.StudentID = B.StudentID，保证配对的两个专业属于同一名学生，从源头排除跨学生的无效组合
• 用A.Major < B.Major代替不等判断：一方面自动排除专业和自身配对的无效记录，另一方面按字符串字典序固定两个专业的先后顺序，避免同一无序组合因为顺序颠倒被重复计数
• 按两个专业字段分组后直接统计对应学生数，DISTINCT是为了兼容表内可能存在的重复学生-专业脏数据，保证计数准确
• 用HAVING过滤学生数小于2的组合，最终按学生数降序返回结果，和预期输出完全匹配

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