总分排名前三的学生

SQL 题: 求总分排名前三的学生。

三张表:

-- Create the course table
CREATE TABLE course (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(200) NOT NULL
);

-- Create the student table
CREATE TABLE student (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(200) NOT NULL
);

-- Create the score table
CREATE TABLE score (
   id INT AUTO_INCREMENT PRIMARY KEY,
   course_id INT NOT NULL,
   stu_id INT NOT NULL,
   score INT NOT NULL
);

-- Insert sample data into the course table
INSERT INTO course (name) VALUES
('Math'),
('Science'),
('History');

-- Insert sample data into the student table
INSERT INTO student (name) VALUES
('John Smith'),
('Alice Johnson'),
('Bob Davis');

-- Insert sample data into the score table
INSERT INTO score (course_id, stu_id, score) VALUES
(1, 1, 95),
(1, 2, 88),
(1, 3, 75),
(2, 1, 92),
(2, 2, 89),
(2, 3, 78),
(3, 1, 87),
(3, 2, 91),
(3, 3, 79);

我们先来看一下学生的分数情况:

select student.name `name`,course.name course,score.score score from score,course,student
where score.stu_id=student.id and course.id=score.course_id;

要求一: 求总分最高的学生，返回学生的姓名和总分

# 1. 求解出最高总分
select SUM(score) from score group by stu_id order by SUM(score) desc limit 1;

# 2. 查询出所有总分等于该值的学生id和总分
select stu.id id,SUM(score) score from student stu,score sc where stu.id=sc.stu_id
group by stu.id having SUM(sc.score) = 
(select SUM(score) from score group by stu_id order by SUM(score) desc limit 1);

# 3. 查询出总分排名第一的学生的相关信息
select stu.name name,t.score from student stu inner join (

select stu.id id,SUM(score) score from student stu,score sc where stu.id=sc.stu_id
group by stu.id having SUM(sc.score) = 
(select SUM(score) from score group by stu_id order by SUM(score) desc limit 1)

) t where t.id=stu.id;

注意点:

可能存在多个学生总分都是最高的
聚合函数必须配合group by使用
当使用group by时,select查询字段只能是分组列或者聚合函数

要求二: 求解单科最高的学生信息

# 1. 求解每一门课程的最高分
select course_id c_id,MAX(score) score from score group by course_id;

# 2. 查询每一课得到最高分的学生信息
select sc.stu_id,c.c_id,c.score from score sc inner join (
   select course_id c_id,MAX(score) score from score group by course_id
 ) c on sc.course_id = c.c_id and sc.score=c.score;
 
 # 3. 补充返回用户名和课程名等信息
select stu.name 学生名,course.name 课程名,c.score 单科最高分数 from ( ( score sc inner join (
   select course_id c_id,MAX(score) score from score group by course_id
 ) c on sc.course_id = c.c_id and sc.score=c.score ) inner join student stu on sc.stu_id=stu.id )
 inner join course on course.id=sc.course_id;

注意点:

多表JOIN怎么写

要求三: 求解总分排名前三的学生信息

# 1. 查询出前三的总分
select distinct SUM(score) from score group by stu_id order by SUM(score) desc limit 3; 

# 2. 查询出所有总分等于该值的学生id和总分
select stu.id id,SUM(score) score from student stu,score sc where stu.id=sc.stu_id
group by stu.id having SUM(sc.score) in (select distinct SUM(score) from score group by stu_id order by SUM(score) desc limit 3);