SQL 学生 课程 分数 2

建表

create table course (
  id integer primary key,
  name text not null
);

create table student (
  id integer primary key,
  name text not null
);

create table score (
  id integer primary key,
  course_id integer not null,
  student_id integer not null,
  score integer not null
);

准备数据

insert into course values (1, '语文'), (2, '数学'),(3,'外语');

insert into student values (1, '小张'), (2, '小王'), (3, '小马'), (4, '小李'),(5,'小赵'),（6, '小孙'）;

#小张只考了语文
insert into score values (1, 1, 1, 80);
#小王考了语文和数学
insert into score values (4,1,2,70),(5,2,2,90);
#小马考了语文、数学、外语
insert into score values (7,1,3,80),(8,2,3,60),(9,3,3,70);
#小李考了语文、数学、外语，外语考了两次
insert into score values (10,1,4,80),(11,2,4,60),(12,3,4,70),(13,3,4,80);
#小赵考了两遍语文
insert into score values (14,1,5,80),(15,1,5,60);
#小孙考了数学和外语
insert into score values (16,2,6,80),(17,3,6,60);

查询考了语文数学的学生，显示姓名，要求两种实现，并且其中一种要用到group by

第一种

select s.id, s.name
from (
	select distinct student_id, course_id
	from score
	where course_id in (1,2)
) sc left join student s 
on sc.student_id = s.id
group by student_id having count(1) = 2;

思路：

先用where条件筛选出考试过语文、数学的学生；

又因为同一个学生可能多次考试同一个科目，所以对子查询中的考试结果去重，得到的结果是一个学生对应一个科目就一条记录；

然后左连接student表后按student_id分组，就会产生一个学生对应多个科目的一组，然后筛选出分组结果中为2个科目的记录。

第二种

select t1.student_id, s.name
from (
	select distinct student_id
	from score
	where course_id = 1
) t1 inner join (
    select distinct student_id
	from score
	where course_id = 2
) t2 on t1.student_id = t2.student_id
left join student s on t1.student_id = s.id;

查询只考了语文数学的学生，显示姓名，要求两种实现，并且其中一种要用到group by

第一种

select s.id, s.name
from (
	select student_id
	from (
		select distinct student_id, course_id
		from score
	) t1
	group by student_id having count(*) = 2
) t2 inner join (
	select distinct student_id
	from score
	where course_id = 1
) t3 on t2.student_id = t3.student_id
inner join (
    select distinct student_id
	from score
	where course_id = 2
) t4 on t2.student_id = t4.student_id
left join student s on t2.student_id = s.id;

查询没有考完所有科目的学生

select s.id, s.name
from (
    select distinct student_id, course_id
    from score
) score_tmp
right join student s on score_tmp.student_id = s.id
group by s.id
having count(*) < (
    select count(*)
    from course
)