题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/students-and-examinations
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
– 1、观察Result table,前3列是Students table和Subjects table的笛卡尔积连接,构成temp表
– 2、将Examinations table按student_id,subject_name分组,计算每个学生参加每一门科目测试的次数,构成temp1表
– 3、将temp表与temp1表左连接
select temp.student_id,temp.student_name,temp.subject_name,ifnull(temp1.counts,0) as attended_exams
from(select s1.student_id,s1.student_name,s2.subject_name
from Students as s1 ,Subjects as s2
)temp left join(
select student_id,subject_name,count(subject_name) as counts
from Examinations
group by student_id,subject_name
)temp1
on temp.student_id=temp1.student_id and temp.subject_name=temp1.subject_name
order by student_id , subject_name