Mysql基础练习题 1280.学生们参加各科测试的次数（力扣）

最新推荐文章于 2024-11-09 19:00:00 发布

花生了什么树~.

最新推荐文章于 2024-11-09 19:00:00 发布

阅读量224

点赞数 7

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_58468790/article/details/141828617

版权

1280.查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

题目链接：

https://leetcode.cn/problems/students-and-examinations/description/

建表插入数据：

Create table If Not Exists Students (student_id int, student_name varchar(20))
Create table If Not Exists Subjects (subject_name varchar(20))
Create table If Not Exists Examinations (student_id int, subject_name varchar(20))
Truncate table Students
insert into Students (student_id, student_name) values ('1', 'Alice')
insert into Students (student_id, student_name) values ('2', 'Bob')
insert into Students (student_id, student_name) values ('13', 'John')
insert into Students (student_id, student_name) values ('6', 'Alex')
Truncate table Subjects
insert into Subjects (subject_name) values ('Math')
insert into Subjects (subject_name) values ('Physics')
insert into Subjects (subject_name) values ('Programming')
Truncate table Examinations
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Programming')
insert into Examinations (student_id, subject_name) values ('2', 'Programming')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Programming')
insert into Examinations (student_id, subject_name) values ('13', 'Physics')
insert into Examinations (student_id, subject_name) values ('2', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Math')

思路分析：

查询每个学生的每一门科目，需要将学生表和科目表进行交叉连接cross join ，再左连接考试表，y用ifnull函数将考试科目为null的赋值为0。

画图分析：

代码实现：

select s.student_id,student_name,sub.subject_name,IFNULL(t1.attended_exams, 0) AS attended_exams
from students  s 
cross join subjects sub
left join
(select student_id,subject_name,count(subject_name) AS attended_exams from examinations group by student_id,subject_name) t1
 on t1.student_id=s.student_id and t1.subject_name=Sub.subject_name 
ORDER BY s.student_id, sub.subject_name;