题目链接：点击打开链接
题目大意：略。
解题思路：略。
AC 代码
-- 解决方案(1) select ss.student_id as student_id, ss.student_name as student_name, ss.subject_name as subject_name, ifnull(e1.attended_exams, 0) as attended_exams from ( select * from `Students` as s1 cross join `Subjects` as s2 ) as ss left join ( select student_id, subject_name, count(student_id) as attended_exams from `Examinations` group by student_id,subject_name ) as e1 on ss.student_id = e1.student_id and ss.subject_name = e1.subject_name order by ss.student_id, ss.subject_name -- 解决方案(2) SELECT s.student_id, student_name, sub.subject_name, COUNT(e.subject_name) attended_exams FROM Subjects sub JOIN Students s LEFT JOIN Examinations e ON e.subject_name = sub.subject_name AND e.student_id = s.student_id GROUP BY s.student_id, sub.subject_name ORDER BY s.student_id, sub.subject_name-- 解决方案(1) select ss.student_id as student_id, ss.student_name as student_name, ss.subject_name as subject_name, ifnull(e1.attended_exams, 0) as attended_exams from ( select * from `Students` as s1 cross join `Subjects` as s2 ) as ss left join ( select student_id, subject_name, count(student_id) as attended_exams from `Examinations` group by student_id,subject_name ) as e1 on ss.student_id = e1.student_id and ss.subject_name = e1.subject_name order by ss.student_id, ss.subject_name -- 解决方案(2) SELECT s.student_id, student_name, sub.subject_name, COUNT(e.subject_name) attended_exams FROM Subjects sub JOIN Students s LEFT JOIN Examinations e ON e.subject_name = sub.subject_name AND e.student_id = s.student_id GROUP BY s.student_id, sub.subject_name ORDER BY s.student_id, sub.subject_name
