题目描述:
解题思路:
它要所有学生的所有科目的测试数目,所以,students和subjects笛卡尔积,然后examinations以它左连接
select st.student_id,st.student_name,st.student_name,count(ex.student_id) attended_exams
from students st
join subjects su
left join examinations ex
on st.student_id = ex.student_id and su.subject_name = ex.subject_name
group by st.student_id,su.subject_name;
表结构:
create table students(
student_id int,
student_name varchar(150),
primary key (student_id)
);
create table subjects(
subject_name varchar(150),
primary key (subject_name)
);
create table Examinations(
student_id int,
subject_name varchar(150)
);
insert into subjects(subject_name) values ('Math');
insert into subjects(subject_name) values ('Physics');
insert into subjects(subject_name) values ('Programming');
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');
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');