面试合集1-sql篇

 学生表s

 成绩表grade

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

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

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

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

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

知识点： 每个科目内部进行排名

ROW_NUMBER() OVER (PARTITION BY kemu ORDER BY score DESC) AS num

 最终结果：

6、计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩

7、计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分

8、列出各门课程的平均成绩，要求显示字段：课程，平均成绩

9、列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名

知识点：


DENSE_RANK() 在遇到相同值时会给予相同的排名，并且后续的排名会紧接着前一个排名，不会跳过任何数字。

RANK() 在处理具有相同排序值的行时会给予它们相同的排名，并且后续排名的数字会跳过，以保持排名的唯一性。

ROW_NUMBER() 不会对具有相同排序值的行分配相同的排名。相反，它会为每一行分配一个不同的数字，即使这些行的排序值相同。

10、列出数学成绩前3名的学生（要求显示字段：学号，姓名, 科目，成绩）

方法1：

方法2：

 11、列出数学成绩第2、3名的学生（要求显示字段：学号，姓名, 科目，成绩）

知识点：limit后面如果只写一个整数n，那就是查询前n条记录；如果后面带2个整数n 和 m，那么第一个数n就是查询出来队列的起点（从0开始），第二个m是统计的总数目

12、 列出数学成绩最后1名的学生（要求显示字段：学号，姓名, 科目，成绩）

 13、统计英语课程少于80分的，显示 学号id, 姓名，科目，分数

 14、统计每门课程不及格（x<60）、一般(60<x<80)、优秀(x>80)

附加知识点：

SQL窗口函数（Window Functions）是一类特殊的函数，它们允许你对数据集的一个“窗口”或子集执行计算，而不需要将数据分组到多个输出行中。这些函数对于执行如排名、累积和、移动平均等操作非常有用，它们可以在不需要改变原始数据行数的情况下，为每一行提供额外的计算值。

窗口函数通常与 OVER() 子句一起使用，该子句定义了窗口或数据集的分区以及排序规则。OVER() 子句可以包含以下部分：

• PARTITION BY：将数据划分为多个分区，窗口函数将在每个分区内独立计算。如果没有指定 PARTITION BY，则整个结果集被视为一个单一的分区。
• ORDER BY：指定窗口内行的排序顺序。这对于许多窗口函数（如 ROW_NUMBER()、RANK()、DENSE_RANK() 以及需要累积计算的函数）是必需的。

以下是一些常见的SQL窗口函数：

1. 排名函数：
   • ROW_NUMBER()：为窗口内的每一行分配一个唯一的连续整数。
   • RANK()：为窗口内的每一行分配一个排名，如果两行具有相同的排序值，则它们将获得相同的排名，并且后续排名的数字会跳过。
   • DENSE_RANK()：类似于 RANK()，但后续排名的数字不会跳过，即使存在重复的排名。
2. 聚合函数作为窗口函数：
   • SUM()、AVG()、MIN()、MAX() 等聚合函数可以作为窗口函数使用，它们允许你在窗口内计算累积和、移动平均等。
3. 值函数：
   • FIRST_VALUE() 和 LAST_VALUE()：分别返回窗口内排序后的第一行和最后一行的值。
   • LAG() 和 LEAD()：分别返回窗口内当前行的前一行和后一行的值，这对于计算变化量或移动平均等非常有用。
4. 其他函数：
   • NTILE(n)：将窗口内的行划分为 n 个桶，并为每一行分配一个桶号。
   • CUME_DIST()、PERCENT_RANK()、NTILE() 等其他高级窗口函数，用于执行更复杂的计算。

使用窗口函数时，需要注意以下几点：

• 窗口函数通常与 SELECT 语句一起使用，并且可以在 SELECT 列表、ORDER BY 子句或 HAVING 子句中出现（但在某些数据库中可能有限制）。
• 窗口函数不能直接在 WHERE 子句中使用，因为它们在逻辑上是在结果集生成后才计算的。但是，你可以使用子查询或公用表表达式（CTE）来绕过这个限制。
• 窗口函数的结果通常依赖于 OVER() 子句中定义的排序和分区规则，因此这些规则必须明确指定。

窗口函数是SQL中一个非常强大且灵活的特性，它们可以极大地简化复杂的数据分析任务。