关于面试总结1-SQL学生表

前言

每次面试必考SQL,小编这几年一直吃SQ的亏，考题无非就是万年不变学生表，看起来虽然简单，真正写出来，还是有一定难度。于是决定重新整理下关于SQL的面试题，也可以帮助更多的人过SQL这一关。
作为一个工作3年以上测试人员，不会sql基本上能拿到offer的希望渺茫，虽然平常也会用到数据库，都是用的简单的查询语句。困难一点的就直接找开发了，面试想留个好印象，还是得熟练掌握，能在纸上快速写出来。

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

万年不变学生表

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

科目和分数表（grade）

排序order by

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

SELECT a.name, b.score
FROM student a, grade b
WHERE a.id = b.id
AND kemu = '数学'
ORDER BY score
DESC

统计总成绩sum

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

SELECT a.name, sum(b.score) as sum_score
FROM student a, grade b
WHERE a.id = b.id
GROUP BY name
DESC

统计总成绩

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

SELECT a.id, a.name, c.sum_score
from student a, 
(SELECT b.id, sum(b.score) as sum_score
FROM grade b
GROUP BY id
) c
WHERE a.id = c.id
ORDER BY sum_score
DESC

统计单科最好成绩

4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩

第一步先group by找出单科最好成绩，作为第一张表

SELECT b.kemu, MAX(b.score)
FROM grade b
GROUP BY kemu

再结合学生表和分数表，得到单科最好成绩

-- 单科最好的成绩
SELECT c.id , a.name, c.kemu, c.score

FROM grade c, student a,
(SELECT b.kemu, MAX(b.score) as max_score
FROM grade b
GROUP BY kemu) t
WHERE c.kemu = t.kemu
AND c.score = t.max_score
AND a.id = c.id

总结 group by相关用法

函数	作用	支持性
sum(列名)	求和
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值
first(列名)	第一条记录	仅Access支持
last(列名)	最后一条记录	仅Access支持
count(列名)	统计记录数	注意和count(*)的区别

各门课程成绩最好的2位学生

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