力扣-高频 SQL 50 题（基础版）-1280. 学生们参加各科测试的次数

一、完整建表 SQLMySQL 语法二、需求查询出每个学生参加每一门科目测试的次数结果按student_id和subject_name排序三、 思路1.插入数据构造表格2.写题表层视觉迷惑点下面这会是大部分人第一次的答案(相似)一定会觉得第二张Subjects表是多余的没必要关联因为Subjects表的仅有的数据Subjects_name刚好第三个表也有所以大部分人一定会在join联合时直接关联Students表Examinations表而舍弃Subjects表# Write your MySQL query statement below select s.student_id,s.student_name,e.subject_name,count(subject_name) attended_exams from Students s join Examinations e on e.student_ids.student_id group by s.student_id,s.student_name,e.subject_name order by s.student_id asc,s.student_name asc展示结果(部分正确)底层逻辑漏洞核心INNER JOIN Students Examinations只会保留有考试记录的「学生 科目」组合学生 Bob 没考过 Physics 物理 → 这条组合直接从结果里消失无法显示次数 0学生 Alex 一门考试都没参加 → 他的 3 门科目全部消失输出里完全看不到 Alex 题目硬性要求每个学生、每一门学校开设科目都要展示没参加考试次数写 0。想要拿到「全部学生 × 全部科目」完整清单必须借助Subjects全科目表做笛卡尔积Subjects 表真实作用存储学校所有开设课程全集不受学生考试行为影响通过 join 和学生表组合强制生成「每个学生匹配全部 3 门科目」的完整行后续再LEFT JOIN考试记录没匹配到考试的行考试字段自动为 NULLCOUNT 统计自动算出 0 次保证输出结果和题目「预期结果」完全一致不会丢失缺考科目、零考试学生。标准满分正确代码# Write your MySQL query statement below SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.subject_name) AS attended_exams FROM Students s -- 第一步交叉连接学生全部科目生成完整笛卡尔积核心依赖Subjects表 CROSS JOIN Subjects sub -- 第二步左连接考试记录保留所有学生科目无考试填充NULL LEFT JOIN Examinations e ON s.student_id e.student_id AND sub.subject_name e.subject_name -- 第三步按学生科目分组统计考试次数 GROUP BY s.student_id, s.student_name, sub.subject_name -- 第四步按题目指定规则排序 ORDER BY s.student_id ASC, sub.subject_name ASC;同类题型通用解题模板总结笔记举一反三题型特征需要展示「所有 A × 所有 B」无匹配数据显示 0必须准备一张全集维度表本题就是 Subjects 科目全集表JOIN主表 × 全集表生成完整组合LEFT JOIN业务记录表本题 ExaminationsCOUNT (业务表字段) 实现无匹配时计数 0GROUP BY 全部非聚合字段ORDER BY 按题目要求排序同类练习题举例统计每个门店每种商品的销量没卖出过显示 0统计每个员工每月考勤天数当月无考勤显示 0核心思路和本题完全一致都需要一张全集维度表做交叉连接拓展如果题目只给Students和Examinations两张表没有专门存储全部科目的表怎么办os我是面试官就这样出题结合生活场景比如大学期末-每场考试都必定至少有一个人参加不存在有课程没有学生参加这个就含蓄地表示Examinations表里拥有原先Subjects里所有的科目这样大学生就会困惑许久核心思路用子查询SELECT DISTINCT subject_name FROM Examinations虚拟生成「科目全集临时集合」替代原本的Subjects表再和学生表交叉连接。完整可运行 SQLSELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.subject_name) AS attended_exams FROM Students s -- 虚拟科目表从考试记录里去重拿到全部存在过的科目 JOIN ( SELECT DISTINCT subject_name FROM Examinations ) AS sub(subject_name) LEFT JOIN Examinations e ON s.student_id e.student_id AND sub.subject_name e.subject_name GROUP BY s.student_id, s.student_name, sub.subject_name ORDER BY s.student_id ASC, sub.subject_name ASC;优缺点✅ 优点不需要物理Subjects表仅靠两张业务表完成需求❌ 缺点如果存在一门全校开设、但从来没有任何学生考过的科目这门课会直接丢失无法展示适用场景题目明确说明所有学校开设的科目都至少有一名学生参加过考试