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

力扣高频SQL 50题（基础版）第十二题

1280. 学生们参加各科测试的次数

题目说明

学生表: Students

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| student_id | int |

| student_name | varchar |

±--------------±--------+

在 SQL 中，主键为 student_id（学生ID）。

该表内的每一行都记录有学校一名学生的信息。

科目表: Subjects

±-------------±--------+

| Column Name | Type |

±-------------±--------+

| subject_name | varchar |

±-------------±--------+

在 SQL 中，主键为 subject_name（科目名称）。

每一行记录学校的一门科目名称。

考试表: Examinations

±-------------±--------+

| Column Name | Type |

±-------------±--------+

| student_id | int |

| subject_name | varchar |

±-------------±--------+

这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。

学生表里的一个学生修读科目表里的每一门科目。

这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

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

思路分析

由于Examinations表中的student_id列没有6号，因此本题需要先把Students表和Subjects表使用笛卡尔积拼接起来，此处拼接后的表为t1，再将t1与Examinations表左连接。

实现过程

准备数据

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')

实现方式

with t1 as (select s1.student_id,s1.student_name,s2.subject_name from Students s1 join Subjects s2)
select t1.student_id,t1.student_name,t1.subject_name,count(e.subject_name) attended_exams
from t1 left join Examinations e on t1.student_id=e.student_id and t1.subject_name=e.subject_name
group by t1.student_id,t1.student_name,t1.subject_name
order by t1.student_id,t1.subject_name;

结果截图