学生表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窗口函数:
- 排名函数:
ROW_NUMBER()
:为窗口内的每一行分配一个唯一的连续整数。RANK()
:为窗口内的每一行分配一个排名,如果两行具有相同的排序值,则它们将获得相同的排名,并且后续排名的数字会跳过。DENSE_RANK()
:类似于RANK()
,但后续排名的数字不会跳过,即使存在重复的排名。
- 聚合函数作为窗口函数:
SUM()
、AVG()
、MIN()
、MAX()
等聚合函数可以作为窗口函数使用,它们允许你在窗口内计算累积和、移动平均等。
- 值函数:
FIRST_VALUE()
和LAST_VALUE()
:分别返回窗口内排序后的第一行和最后一行的值。LAG()
和LEAD()
:分别返回窗口内当前行的前一行和后一行的值,这对于计算变化量或移动平均等非常有用。
- 其他函数:
NTILE(n)
:将窗口内的行划分为n
个桶,并为每一行分配一个桶号。CUME_DIST()
、PERCENT_RANK()
、NTILE()
等其他高级窗口函数,用于执行更复杂的计算。
使用窗口函数时,需要注意以下几点:
- 窗口函数通常与
SELECT
语句一起使用,并且可以在SELECT
列表、ORDER BY
子句或HAVING
子句中出现(但在某些数据库中可能有限制)。 - 窗口函数不能直接在
WHERE
子句中使用,因为它们在逻辑上是在结果集生成后才计算的。但是,你可以使用子查询或公用表表达式(CTE)来绕过这个限制。 - 窗口函数的结果通常依赖于
OVER()
子句中定义的排序和分区规则,因此这些规则必须明确指定。
窗口函数是SQL中一个非常强大且灵活的特性,它们可以极大地简化复杂的数据分析任务。
标签:窗口,函数,RANK,面试,姓名,sql,排名,成绩,合集 From: https://www.cnblogs.com/yudx/p/18543366