做像素畫的網(wǎng)站岳陽seo公司
前述
知識(shí)點(diǎn)回顧:數(shù)據(jù)庫中的四大join & 笛卡爾乘積(以MySQL為例)
- 笛卡爾積的兩種寫法
select * from stu,class;
select * from stu cross join class;
題目描述
leetcode題目:1280. 學(xué)生們參加各科測試的次數(shù)
Code
寫法一
先把Students表和Subjects表進(jìn)行笛卡爾積,得到表S
再左外連接統(tǒng)計(jì)好的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