007、【上海悠悠】sql面试题_1，学生表、成绩表

万年不变学生表

有2张表，学生表（student）基本信息如下：

 科目和分数表（grade）如下：

 

问：

• 1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低
• 2.统计每个学生的总成绩,显示字段：姓名，总成绩
• 3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩
• 4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩
• 5.列出各门课程成绩最好的2位学生， 要求显示字段: 学号，姓名, 科目，成绩

 

个人解答如下：

# 1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低

# 执行结果成功。

select s.name, g.kemu, g.score
from student s join grade g on s.id=g.id
where g.kemu='数学'
order by g.score desc;


# 2.统计每个学生的总成绩,显示字段：姓名，总成绩

# 执行结果成功。

select s.name, sum(g.score)
from student s join grade g on s.id=g.id
group by s.id;


# 3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩
# 执行结果成功。

select s.id, s.name, sum(g.score)
from student s join grade g on s.id=g.id
group by s.id;


# 4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩
select s.id,s.name,g.kemu,g.score
from student s join grade g on s.id=g.id
join (select g.kemu, max(g.score) as max_score from grade g group by g.kemu) t on t.kemu=g.kemu
where t.max_score=g.score and g.id=s.id;


# 5.列出各门课程成绩最好的2位学生， 要求显示字段: 学号，姓名, 科目，成绩
# 答案看不懂。
SELECT t1.id, a.name, t1.kemu, t1.score
FROM grade t1, student a
WHERE
(SELECT count(*) FROM grade t2 WHERE t1.kemu=t2.kemu AND t2.score>t1.score)<2
and a.id = t1.id
ORDER BY t1.kemu,t1.score DESC