MySQL 查询数据生成排序序号

在实际开发中，我们经常需要对查询到的数据进行排序，并为每条数据生成一个排序序号。这个排序序号可以用来展示排名、打印列表等等。在MySQL中，我们可以通过一些技巧来实现这个需求，本文将详细介绍如何使用MySQL查询数据生成排序序号。

1. 数据库结构与样例数据

首先，我们需要创建一个数据表，并插入一些样例数据。假设我们有一个电影表（movies），包含以下字段：

id: 电影ID，自增主键
title: 电影标题
release_year: 发布年份
rating: 评分

创建数据表的SQL语句如下：

CREATE TABLE movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year INT,
    rating FLOAT
);

插入一些样例数据：

INSERT INTO movies (title, release_year, rating) VALUES
    ('Avatar', 2009, 7.8),
    ('Titanic', 1997, 7.8),
    ('Star Wars: The Force Awakens', 2015, 7.8),
    ('Jurassic World', 2015, 7.0),
    ('The Lion King', 2019, 6.9),
    ('The Avengers', 2012, 8.0),
    ('Furious 7', 2015, 7.2),
    ('Avengers: Age of Ultron', 2015, 7.3),
    ('Frozen II', 2019, 6.9),
    ('Black Panther', 2018, 7.3);

2. 查询数据并生成排序序号

现在，我们开始编写SQL语句，查询数据并生成排序序号。

SELECT 
    (@row_number:=@row_number + 1) AS row_number,
    title,
    release_year,
    rating
FROM
    movies,
    (SELECT @row_number:=0) AS t
ORDER BY
    rating DESC;

上述SQL语句中，我们使用了一个MySQL用户变量 @row_number，并对它进行自增操作 @row_number:=@row_number + 1。同时，我们使用了一个子查询 (SELECT @row_number:=0) AS t 来初始化 @row_number 变量。

在查询结果中，我们为每条数据生成了一个排序序号 row_number，并按照 rating 字段进行降序排序。

运行上述SQL语句后，可以得到以下结果：

+------------+--------------------------+--------------+--------+
| row_number | title                    | release_year | rating |
+------------+--------------------------+--------------+--------+
|          1 | The Avengers             |         2012 |    8.0 |
|          2 | Avatar                   |         2009 |    7.8 |
|          3 | Star Wars: The Force...  |         2015 |    7.8 |
|          4 | Titanic                  |         1997 |    7.8 |
|          5 | Avengers: Age of Ultron  |         2015 |    7.3 |
|          6 | Black Panther            |         2018 |    7.3 |
|          7 | Furious 7                |         2015 |    7.2 |
|          8 | Jurassic World           |         2015 |    7.0 |
|          9 | The Lion King            |         2019 |    6.9 |
|         10 | Frozen II                |         2019 |    6.9 |
+------------+--------------------------+--------------+--------+

可以看到，每条数据都有了一个排序序号，并且按照评分字段 rating 进行了降序排序。

3. 添加其他条件与限制

除了排序和生成排序序号外，我们可能还需要添加其他查询条件和限制。

例如，我们可以通过添加 WHERE 子句来过滤指定条件的数据：

SELECT 
    (@row_number:=@row_number + 1) AS row_number,
    title,
    release_year,
    rating
FROM
    movies,
    (SELECT @row_number:=0) AS t
WHERE
    release_year > 2010
ORDER BY
    rating DESC;

上述SQL语句中，我们添加了 WHERE release_year > 2010，用于过滤在2010年之后发布的电影。运行此SQL语句后，可以得到如下结果：

+------------+--------------------------+--------------+--------+
| row_number | title                    | release_year | rating |
+------------+--------------------------+--------------+--------+
|          1 | The Avengers             |         2012 |    8.0 |
|          2 | Star Wars: The Force...  |         2015 |    7.8 |
|          3 | Avengers: Age of Ultron  |         2015 |    7.3 |
|          4 | Black Panther            |         2018 |    7.3 |
|          5 | Jurassic World           |         2015 |    7.0 |
|          6 | The Lion King            |         2019 |    6.9 |
|          7 | Frozen II                |         2019 |    6.9 |
+------------+--------------------------+--------------+--------+

可以看到，结果中只包含了2010年之后发布的电影，并且按照评分字段进行了降序排序。

此外，我们还可以使用 LIMIT 子句限制查询结果的数量：

SELECT 
    (@row_number:=@row_number + 1) AS row_number,
    title,
    release_year,
    rating
FROM
    movies,
    (SELECT @row_number:=0) AS t
ORDER BY
    rating DESC
LIMIT 3;

上述SQL语句中，我们添加了 LIMIT 3，表示只查询前3条结果。运行此SQL语句后，可以得到如下结果：

+------------+--------------------------+--------------+--------+
| row_number | title                    | release_year | rating |
+------------+--------------------------+--------------+--------+
|          1 | The Avengers             |         2012 |    8.0 |
|          2 | Avatar                   |         2009 |    7.8 |
|          3 | Star Wars: The Force...  |         2015 |    7.8 |
+------------+--------------------------+--------------+--------+

可以看到，结果中只包含了前3条数据。

4. 防止排序序号混乱

在上述示例中，我们使用了MySQL用户变量 @row_number 来生成排序序号。需要注意的是，如果查询语句中存在多个 @row_number 变量，可能会导致排序序号混乱。

为了防止这种情况的发生，我们可以为每个查询使用不同的变量名。

以下是一个示例：

SELECT 
    (@row_number1:=@row_number1 + 1) AS row_number,
    title,
    release_year,
    rating
FROM
    movies,
    (SELECT @row_number1:=0) AS t
ORDER BY
    rating DESC;

SELECT 
    (@row_number2:=@row_number2 + 1) AS row_number,
    title,
    release_year,
    rating
FROM
    movies,
    (SELECT @row_number2:=0) AS t
ORDER BY
    release_year DESC;

在两个查询中，我们分别使用了 @row_number1 和 @row_number2 作为排序序号的变量。可以看到，使用不同的变量名可以确保每个查询生成的排序序号不会混乱。