有 3 个表:学生表、科目表和考试表。
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math |
| Physics |
| Programming |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1 | Math |
| 1 | Physics |
| 1 | Programming |
| 2 | Programming |
| 1 | Physics |
| 1 | Math |
| 13 | Math |
| 13 | Programming |
| 13 | Physics |
| 2 | Math |
| 1 | Math |
+------------+--------------+
SQL代码如下:
SELECT s.student_id,s.student_name,b.subject_name,COUNT(e.subject_name) as attended_exams
FROM Students as s
INNER JOIN Subjects as b
LEFT JOIN Examinations as e
ON s.student_id=e.student_id AND b.subject_name=e.subject_name
GROUP BY s.student_id,b.subject_name;
我感到困惑的部分是INNER JOIN
因为"学生"表和"科目"表没有交集或公共列。我想我的目标是直观地理解/想象LEFT JOIN
执行之前的表格。而且,既然我们已经在考试表中拥有所有科目,是否有必要包括主题表?非常感谢您的帮助!
这实际上是LeetCode数据库的问题。链接如下:
https://leetcode.com/problems/students-and-examinations/
查询等效于:
SELECT s.student_id, s.student_name, b.subject_name,
COUNT(e.subject_name) as attended_exams
FROM Students s CROSS JOIN
Subjects b LEFT JOIN
Examinations as e
ON s.student_id = e.student_id AND
b.subject_name = e.subject_name
GROUP BY s.student_id, b.subject_name;
MySQL扩展了JOIN
语法,因此ON
子句是可选的。 就个人而言,我认为这是一个非常糟糕的主意。CROSS JOIN
是一种CROSS JOIN
,应这样指定。
查询的作用是为每个学生/科目组合生成一行。 然后计算每个学生在每个科目中参加了多少次考试。
在 MySql 中,使用join
或inner join
时,on
条件是可选的,如果未指定,则查询等效于交叉联接。更详细的解决方案在这里
带主题"表的JOIN
。您可能还需要为亚历克斯IFNULL()
,不幸的是,他没有参加任何考试。
试试这个:
SELECT
s.student_id, s.student_name, e.subject_name, IFNULL(COUNT(e.subject_name), 0) as attended_exams
FROM Students as s
LEFT JOIN Examinations as e
ON s.student_id = e.student_id
GROUP BY s.student_id, e.subject_name
;
正如雷纳托刚刚指出的那样,这是学生和科目之间的交叉连接。 其余的只是一种标记学生是否参加该科目考试的方法,因为COUNT
不会计算空值。