mysql如何使用rownum

原创

mob649e8167c4a3 2023-08-23 13:27:18 ©著作权

文章标签 MySQL 分页结果集 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob649e8167c4a3的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL如何使用rownum

引言

在数据库查询中，我们经常需要对查询结果进行排序、分页或者限制结果集的数量。在MySQL中，我们可以使用rownum关键字来实现这些需求。本文将介绍MySQL中如何使用rownum关键字，并通过解决一个实际问题来说明其用法。

什么是rownum

rownum是MySQL中的一个虚拟列，它用于给查询结果中的每一行分配一个唯一的序号。这个序号是根据查询结果的顺序来分配的，从1开始递增。我们可以使用rownum来实现对查询结果进行排序、分页或者限制结果集的数量。

解决一个实际问题

假设我们有一个学生信息表，其中包含学生的姓名、年龄和成绩。我们希望按照成绩的降序排列，并且只取前10名学生的信息。我们可以使用rownum来解决这个问题。

首先，我们创建一个名为students的表，并插入一些测试数据。

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score INT
);

INSERT INTO students (name, age, score) VALUES 
  ('Tom', 18, 90),
  ('Jerry', 19, 85),
  ('Alice', 20, 95),
  ('Bob', 18, 92),
  ('Lisa', 19, 88),
  ('Mike', 20, 87),
  ('John', 18, 89),
  ('Amy', 19, 93),
  ('David', 20, 91),
  ('Lily', 18, 86),
  ('Sam', 19, 94),
  ('Emily', 20, 90);

接下来，我们使用以下SQL查询语句来按照成绩的降序排列，并且只取前10名学生的信息。

SELECT 
  rownum, name, age, score
FROM
  (SELECT 
    @rownum := @rownum + 1 AS rownum, name, age, score
  FROM
    students, (SELECT @rownum := 0) AS r
  ORDER BY score DESC) AS t
WHERE 
  rownum <= 10;

在上面的查询语句中，我们首先使用内部查询将rownum列加入到查询结果中。这里使用了MySQL的用户变量@rownum来计算序号，初始值设为0。然后，我们按照成绩的降序排列查询结果，并将它作为子查询t。最后，我们使用外部查询来限制结果集的数量，只取序号小于等于10的学生信息。

查询结果如下：

+--------+-------+-----+-------+
| rownum | name  | age | score |
+--------+-------+-----+-------+
|      1 | Alice |  20 |    95 |
|      2 | Amy   |  19 |    93 |
|      3 | Sam   |  19 |    94 |
|      4 | David |  20 |    91 |
|      5 | Tom   |  18 |    90 |
|      6 | Emily |  20 |    90 |
|      7 | Bob   |  18 |    92 |
|      8 | John  |  18 |    89 |
|      9 | Lisa  |  19 |    88 |
|     10 | Mike  |  20 |    87 |
+--------+-------+-----+-------+