2021-06-01

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

需求：写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

展示效果：

+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+

建表语句：

Create table If Not Exists 68_Students (student_id int, student_name varchar(20));
Create table If Not Exists 68_Subjects (subject_name varchar(20));
Create table If Not Exists 68_Examinations (student_id int, subject_name varchar(20));
Truncate table 68_Students;
insert into 68_Students (student_id, student_name) values ('1', 'Alice');
insert into 68_Students (student_id, student_name) values ('2', 'Bob');
insert into 68_Students (student_id, student_name) values ('13', 'John');
insert into 68_Students (student_id, student_name) values ('6', 'Alex');
Truncate table 68_Subjects;
insert into 68_Subjects (subject_name) values ('Math');
insert into 68_Subjects (subject_name) values ('Physics');
insert into 68_Subjects (subject_name) values ('Programming');
Truncate table 68_Examinations;
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');

最终sql：

SELECT 
     a.student_id,
     a.student_name,
     b.subject_name,
     COUNT(e.subject_name) AS attended_exams
FROM 
     68_Students a 
CROSS JOIN
     68_Subjects b
LEFT JOIN
     68_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;

答案2：

SELECT 
		t.student_id, t.student_name,t.subject_name,IF(t1.count is not null ,t1.count,0)
from
		(SELECT 
         student_id, student_name,subject_name
         from
         68_Students
         FULL JOIN
         68_Subjects) t
LEFT JOIN
        (select 
         student_id,subject_name,COUNT(*) count
         from
         68_Examinations
         GROUP BY
         student_id, subject_name) t1
on 
		t.student_id = t1.student_id
and
		t.subject_name = t1.subject_name
ORDER BY
		student_id,subject_name