MySQL查询排名的2种方式：变量排名、窗口函数

2024-07-02 23:52| 来源: 网络整理| 查看: 265

如果你想在MySQL查询中进行排名，可以使用以下几种方法：

使用变量进行排名：

以下是一个基于分数对学生进行排名的示例：

SET @rank = 0; SELECT @rank := @rank + 1 AS rank, student_id, student_name, score FROM (SELECT student_id, student_name, score FROM students ORDER BY score DESC) ranked_students;

这个查询首先根据分数对学生进行排序，然后使用变量@rank生成排名。

相同名次

在MySQL中，如果你希望为具有相同值的记录分配相同的名次，你可以使用用户变量来实现。以下是一个示例，其中我们使用变量@rank、@prev_score和@row_num来处理相同名次的问题：

SET @rank := 0; SET @prev_score := NULL; SET @row_num := 0; SELECT student_id, student_name, score, IF(@prev_score = score, @rank, @rank := @row_num) AS rank, @prev_score := score, @row_num := @row_num + 1 FROM (SELECT student_id, student_name, score FROM students ORDER BY score DESC) ranked_students;

在这个示例中：

@rank 变量用于存储当前的排名。 @prev_score 变量用于存储前一行的分数，用于比较当前行和前一行的分数是否相等。 @row_num 变量用于存储行号，每处理一行数据时自动递增。

查询逻辑如下：

对学生按照分数降序排列。遍历排序后的结果，并检查当前行的分数是否与前一行的分数相等。如果分数相等，则分配相同的排名；否则，将排名设置为当前行号。更新@prev_score和@row_num变量。

注意，这些示例都是基于一个假设的students表的。根据你的实际数据表结构和需求，你可能需要对这些示例进行调整。

分组排名

以下是一个根据班级和分数对学生进行排名的示例：

SET @rank := 0; SET @prev_class := ''; SELECT class_id, student_id, student_name, score, IF(@prev_class = class_id, @rank := @rank + 1, @rank := 1) AS rank_in_class, @prev_class := class_id FROM (SELECT class_id, student_id, student_name, score FROM students ORDER BY class_id, score DESC) ranked_students; 查询特定学生的排名

以下是一个查询特定学生排名的示例：

SET @rank := 0; SELECT rank, student_id, student_name, score FROM (SELECT @rank := @rank + 1 AS rank, student_id, student_name, score FROM (SELECT student_id, student_name, score FROM students ORDER BY score DESC) sorted_students) ranked_students WHERE student_id = 123;

这些示例都是基于一个假设的students表的。根据你的实际数据表结构和需求，你可能需要对这些示例进行调整。

使用窗口函数进行排名（自MySQL 8.0起可用）：

MySQL 8.0及更高版本支持窗口函数，可用于生成排名。以下是一些常用的窗口函数：

ROW_NUMBER(): 分配一个唯一的整数，表示每一行的顺序。 RANK(): 分配一个唯一的整数，表示每一行的排名，相同的值将获得相同的排名，排名之间可能会有间隔。 DENSE_RANK(): 分配一个唯一的整数，表示每一行的排名，相同的值将获得相同的排名，排名之间没有间隔。

以下是使用RANK()函数进行排名的示例：

SELECT student_id, student_name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students;

这个查询将为students表中的每一行分配一个基于分数的排名。如果你希望使用不同的窗口函数，只需将RANK()替换为ROW_NUMBER()或DENSE_RANK()即可。

下面我们继续讨论其他MySQL查询排名相关的内容。

分组排名

有时候，你可能需要在某个组内进行排名。例如，你可能希望按照班级对学生的分数进行排名。这时，你可以在窗口函数中使用PARTITION BY子句。以下是一个示例：

SELECT student_id, student_name, class_id, score, RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_in_class FROM students;

在这个查询中，RANK()函数首先将学生按照班级进行分组，然后在每个班级内按照分数进行排名。

分页排名查询

有时候，你可能想要查看排名的某个范围，例如，查看排名在前10名的学生。这可以通过LIMIT子句实现。以下是一个示例：

SELECT student_id, student_name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students ORDER BY score DESC LIMIT 10;

这个查询将返回分数排名前10名的学生。

查询某个学生的排名

如果你想要查询某个特定学生的排名，可以使用子查询。以下是一个示例：

SELECT student_id, student_name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students WHERE student_id = 123;

在这个查询中，我们首先计算所有学生的排名，然后通过WHERE子句筛选出特定学生的排名。

相同名次

使用窗口函数在MySQL 8.0及更高版本中处理相同名次的问题非常简单。你可以使用RANK()或DENSE_RANK()窗口函数来实现。

以下是使用RANK()窗口函数为具有相同分数的学生分配相同名次的示例：

SELECT student_id, student_name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students;

在这些查询中，RANK()或DENSE_RANK()窗口函数会根据分数对学生进行排名，具有相同分数的学生将获得相同的名次。RANK()函数在相同名次之后会留下空缺，而DENSE_RANK()则不会。

【本文地址】

公司简介

联系我们