基于兴趣领域统计学生人数的SQL查询问题及调试尝试

需求 #

编写SQL查询语句，输出每个兴趣领域及对该领域感兴趣的学生人数。

学生表数据 #

+------+----------+----------+
| sno  | sub1     | sub2     |
+------+----------+----------+
| s1   | ds       | networks |
| s2   | os       | ds       |
| s3   | ds       | os       |
| s4   | networks | db       |
| s5   | os       | networks |
| s6   | db       | ds       |
| s7   | networks | os       |
| s8   | db       | os       |
+------+----------+----------+

预期输出（注：输出中的ob为笔误，实际应为os） #

+----------+-------------+
| sub1     |Totalstudents|
+----------+-------------+
| db       |           3 |
| ds       |           4 |
| networks |           4 |
| os       |           5 |
+----------+-------------+

你尝试的SQL语句问题分析 #

• 第一条语句仅统计了sub1列的领域数据，完全忽略sub2列的兴趣领域，导致统计结果缺失。
• 第二条语句使用笛卡尔积关联表，逻辑错误，会产生重复统计，且无法覆盖所有兴趣领域的情况。

正确的SQL查询语句 #

SELECT sub AS sub1, COUNT(DISTINCT sno) AS Totalstudents
FROM (
    -- 将每个学生的两个兴趣领域拆分为单独行
    SELECT sno, sub1 AS sub FROM student
    UNION ALL
    SELECT sno, sub2 AS sub FROM student
) AS combined_subs
GROUP BY sub
ORDER BY sub;

语句说明 #

• 用UNION ALL把每个学生的sub1和sub2合并成包含所有兴趣领域的临时结果集，每个学生的每个兴趣对应一行数据。
• 对临时结果集按兴趣领域分组，COUNT(DISTINCT sno)确保同一个学生不会因多个兴趣被重复计数。
• 最后按领域名称排序，让结果更规整。

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