网站开发周总结,北京设计院,永久短链接,正规企业网站开发使用方法前述
知识点回顾#xff1a;数据库中的四大join 笛卡尔乘积#xff08;以MySQL为例#xff09;
笛卡尔积的两种写法 select * from stu,class; select * from stu cross join class;
题目描述
leetcode题目#xff1a;1280. 学生们参加各科测试的次数 Code
写法…前述
知识点回顾数据库中的四大join 笛卡尔乘积以MySQL为例
笛卡尔积的两种写法 select * from stu,class; select * from stu cross join class;
题目描述
leetcode题目1280. 学生们参加各科测试的次数 Code
写法一
先把Students表和Subjects表进行笛卡尔积得到表S 再左外连接统计好的E表
select S.student_id, S.student_name,S.subject_name,ifnull(cnt, 0) as attended_exams
from (select *from Students, Subjects
) S
left join (select *, count(*) as cntfrom Examinationsgroup by student_id, subject_name
) E
on S.student_id E.student_id and S.subject_name E.subject_name
order by S.student_id, S.subject_name写法二
select Stu.student_id, Stu.student_name,Sub.subject_name,ifnull(cnt, 0) as attended_exams
from Students Stu
cross join Subjects Sub
left join (select *, count(*) as cntfrom Examinationsgroup by student_id, subject_name
) E
on Stu.student_id E.student_id and Sub.subject_name E.subject_name
order by Stu.student_id, Sub.subject_name
