《学习MySQL语句》——第十八天

shawn&lycheeo

已于 2023-04-28 14:40:12 修改

阅读量55

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库学习

于 2023-04-27 16:27:16 首次发布

本文链接：https://blog.csdn.net/qq_40911499/article/details/130409702

版权

MySQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

# Write your MySQL query statement below

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


#使用笛卡尔积，cross join将学生和表对应起来
#不能用COUNT(*)，要用COUNT(e.subject_name)，因为e.subject_name中才有NULL