查看“SQL 学生课程分数”的源代码

=== 建表 ===
<syntaxhighlight lang="sql">
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
);
</syntaxhighlight>

=== 准备数据 ===
<syntaxhighlight lang="sql">
insert into course values (1, '语文'), (2, '数学'),(3,'外语');
insert into student values (1, '小张'), (2, '小王'), (3, '小马');

insert into score values (1, 1, 1, 80), (2,2,1,90), (3,3,1,70);
insert into score values (4,1,2,70),(5,2,2,90),(6,3,2,80);
insert into score values (7,1,3,80),(8,2,3,60),(9,3,3,70);
</syntaxhighlight>

=== 查询总分最高的学生分数是多少 ===
先查最高分是多少<syntaxhighlight lang="sql">
select student_id, sum(score)
from score
group by student_id
order by sum(score) desc
limit 1;
</syntaxhighlight>但其实可能存在多个学生的总分一样：<syntaxhighlight lang="sql">
select student_id, sum(score)
from score
group by student_id
order by sum(score) desc;
</syntaxhighlight>
{| class="wikitable"
|+
!student_id
!sum(score)
|-
|1
|240
|-
|2
|240
|-
|3
|210
|}
所以应该是：<syntaxhighlight lang="sql">
select distinct (student_id)
from score 
group by student_id 
having sum(score) = (
  select sum(score)
  from score 
  group by student_id
  order by sum(score) desc
  limit 1
)
</syntaxhighlight>在进行连接查询展示出姓名就是：<syntaxhighlight lang="sql">
select s.name, t.score
from student s 
right join  (

	select distinct (student_id), sum(score) score
	from score 
	group by student_id 
	having sum(score) = (
	  select sum(score)
	  from score 
	  group by student_id
	  order by sum(score) desc
	  limit 1
	)

) t on s.id = t.student_id
</syntaxhighlight>
=== 查询单科分数最高的学生分数是多少 ===
先查出单科最高分：<syntaxhighlight lang="sql">
select max(score) score, course_id
from score group by course_id;
</syntaxhighlight>查除取得这个课程最高分的所有学生：<syntaxhighlight lang="sql">
select student_id, s1.course_id, s2.max_score
from score s1 
right join (

	select max(score) max_score, course_id
	from score 
	group by course_id

) s2 on s1.course_id = s2.course_id 
and s1.score = s2.max_score;
</syntaxhighlight>再连接查询学生表和课程表显示学生名和课程名：<syntaxhighlight lang="sql">
select s.name, c.name, s2.max_score
from score s1 
right join (

	select max(score) max_score, course_id
	from score 
	group by course_id

) s2 on s1.course_id = s2.course_id 
and s1.score = s2.max_score
left join course c on c.id = s1.course_id
left join student s on s.id = student_id;
</syntaxhighlight>