mysql查询groupby后的row_number|极客笔记

mysql查询groupby后的row_number

在进行 SQL 查询时，经常会使用 GROUP BY 进行分组，但在某些情况下我们也希望对每个分组内的数据进行编号。在 MySQL 中并没有直接提供 row_number() 函数用于实现这一功能，但我们可以通过一些技巧来模拟实现。

在本文中，我们将介绍如何在 MySQL 中实现对 GROUP BY 分组后的数据进行编号，以及一些常见的应用场景和示例代码。

实现方法

要实现对 GROUP BY 分组后的数据进行编号，可以借助于变量来实现。具体步骤如下：

使用一个变量来保存行号，并初始化为1。
在查询结果中添加一个字段，通过 CASE WHEN 语句来根据当前行号进行赋值。
在 GROUP BY 分组之前，先进行排序，确保数据在分组内是有序的。

下面我们通过一个示例来演示如何实现这个功能。

示例

假设我们有一个名为 orders 的表，用于保存订单信息，包括订单号、客户姓名和订单金额。我们希望对客户姓名进行分组，并在每个客户组内对订单按金额进行排名。

首先，创建 orders 表并插入测试数据：

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(50),
    order_amount DECIMAL(10, 2)
);

INSERT INTO orders VALUES
(1, 'Alice', 100.00),
(2, 'Bob', 150.00),
(3, 'Alice', 200.00),
(4, 'Bob', 120.00),
(5, 'Alice', 180.00);

接下来，编写 SQL 查询语句来对订单表进行分组，并对每个客户组内的订单按金额进行排名：

SET @row_number = 0;
SET @prev_customer_name = '';

SELECT
    *,
    CASE
        WHEN @prev_customer_name = customer_name THEN
            @row_number := @row_number + 1
        ELSE
            @row_number := 1
    END AS row_number,
    @prev_customer_name := customer_name
FROM (
    SELECT
        order_id,
        customer_name,
        order_amount
    FROM orders
    ORDER BY customer_name, order_amount
) AS sorted_orders;

在上面的查询中，我们首先初始化 @row_number 和 @prev_customer_name 变量，然后通过 CASE 语句来判断当前客户姓名是否和上一行相同，如果相同则行号加1，否则行号重置为1。最后输出查询结果。

运行结果

运行上面的查询语句后，将得到如下结果：

+----------+---------------+--------------+------------+
| order_id | customer_name | order_amount | row_number |
+----------+---------------+--------------+------------+
|        1 | Alice         |       100.00 |          1 |
|        5 | Alice         |       180.00 |          2 |
|        3 | Alice         |       200.00 |          3 |
|        2 | Bob           |       120.00 |          1 |
|        4 | Bob           |       150.00 |          2 |
+----------+---------------+--------------+------------+

从结果可以看出，我们成功对客户姓名进行了分组，并在每个客户组内按订单金额进行了排名。