1280. Students and Examinations

student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.

Table: Subjects

subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.

Table: Examinations

There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.

解答:

SELECT s.student_id , s.student_name , sub.subject_name , COUNT(e.student_id) AS attended_exams
FROM Students s 
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
ON s.student_id = e.student_id
AND e.subject_name = sub.subject_name
GROUP BY s.student_id , s.student_name , sub.subject_name
ORDER BY s.student_id , sub.subject_name

解題思路：

先下這段sql,

SELECT s.student_id , s.student_name , sub.subject_name
FROM Students s 
CROSS JOIN Subjects sub

拿到兩張表格把所有的可能排列組合出來

學習點：
1.CROSS JOIN 把表的所有排列組合取出來
2.GROUP BY 把要分的student_id ,student_name ,subject_name分出來