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

https://leetcode.cn/problems/students-and-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');

四.解题思路

select  st.student_id,st.student_name,su.subject_name, count(e.subject_name)  as attended_exams  from students st
cross join subjects su
left join Examinations e on e.student_id=st.student_id and e.subject_name=su.subject_name
group by st.student_id,st.student_name,su.subject_name
order by st.student_id,st.student_name;

1. 选择字段

  select st.student_id, st.student_name, su.subject_name, count(e.subject_name) as attended_exams
   

   这部分选择了学生ID（`st.student_id`）、学生姓名（`st.student_name`）、科目名称（`su.subject_name`）和参加考试的次数（`count(e.subject_name)`）。

2. 表连接
   

from students st
   cross join subjects su
   left join Examinations e on e.student_id=st.student_id and e.subject_name=su.subject_name

   
   
   students st`：表示从`students`表中选择数据，别名为`st`。
   cross join subjects su`：表示与`subjects`表进行笛卡尔积连接，别名为`su`。这意味着每个学生都会与每个科目配对。
   left join Examinations e on e.student_id=st.student_id and e.subject_name=su.subject_name`：表示左连接`Examinations`表，别名为`e`。连接条件是学生ID和科目名称匹配。

3. 分组
 
   

group by st.student_id, st.student_name, su.subject_name

   这部分表示按照学生ID、学生姓名和科目名称进行分组。由于使用了`cross join`，每个学生和每个科目都会形成一个组合，然后左连接`Examinations`表，计算每个学生对于每个科目的考试次数。

4. 排序
   

   order by st.student_id, st.student_name

   
   这部分表示按照学生ID和学生姓名进行排序。

五.代码优化

select st.student_id, st.student_name, su.subject_name, count(e.subject_name) as attended_exams
from students st
cross join subjects su
left join Examinations e on e.student_id = st.student_id and e.subject_name = su.subject_name
group by st.student_id, st.student_name, su.subject_name
order by st.student_id, st.student_name;