上海做得好的网站建设公司上海外贸出口代理公司排名
ROW_NUMBER() 是 MySQL8引入的窗口函数之一，它为查询结果集中的每一行分配一个唯一的顺序号（行号）。这个顺序号是基于窗口函数的 ORDER BY 子句进行排序的，可以根据指定的排序顺序生成连续的整数值。
ROW_NUMBER() 在分页、去重、分组内排序等场景中非常有用。
本文涉及到的脚本测试请在个人测试库进行。
使用场景
- **分页查询：**使用
ROW_NUMBER()可以生成每行的序号，结合WHERE或LIMIT子句实现高效的分页查询。尤其是在没有OFFSET支持的情况下，ROW_NUMBER()允许你在分页时进行灵活的排序。 - **去除重复数据：**可以利用
ROW_NUMBER()来给每一行打上唯一标识，之后选择每组的第一行，从而有效地去除重复数据。 - **分组内排序：**可以按组对数据进行排序，并为每个组中的行分配一个行号。这个场景通常用于比如给每个订单中的商品按价格排序，并为每个订单挑选排名第一的商品。
- **数据排名：**使用
ROW_NUMBER()可以为查询结果中的数据进行排名，适用于例如学生成绩排名、销售业绩排名等场景。
语法
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression) AS row_num
PARTITION BY：可选，按指定字段分组。相同分组内的行号会重新从 1 开始。ORDER BY：指定排序字段，行号的生成顺序由此决定。
示例
假设有一个电商数据库，包含 orders 和 order_items 表，使用 ROW_NUMBER() 来展示几种常见场景。
示例 1：为每个订单中的商品按价格排名
可以为每个订单中的商品按价格进行排序，并为每个商品分配一个排名。
-- 创建 orders 表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_name VARCHAR(100),order_date DATE
);-- 创建 order_items 表
CREATE TABLE order_items (order_item_id INT AUTO_INCREMENT PRIMARY KEY,order_id INT,product_name VARCHAR(100),quantity INT,unit_price DECIMAL(10, 2),FOREIGN KEY (order_id) REFERENCES orders(order_id)
);-- 插入数据
INSERT INTO orders (customer_name, order_date) VALUES
('Alice', '2024-10-01'),
('Bob', '2024-10-02'),
('Charlie', '2024-10-03');INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),
(1, 'Phone', 2, 500.00),
(1, 'Tablet', 1, 300.00),
(2, 'Headphones', 2, 100.00),
(2, 'Mouse', 1, 50.00),
(3, 'Smartwatch', 1, 150.00),
(3, 'Laptop', 1, 800.00);
查询：为每个订单中的商品按 unit_price 排序，给出排名
SELECT oi.order_id, oi.product_name, oi.unit_price,ROW_NUMBER() OVER (PARTITION BY oi.order_id ORDER BY oi.unit_price DESC) AS `rank`
FROM order_items oi;
结果
| order_id | product_name | unit_price | rank |
|---|---|---|---|
| 1 | Laptop | 1000.00 | 1 |
| 1 | Phone | 500.00 | 2 |
| 1 | Tablet | 300.00 | 3 |
| 2 | Headphones | 100.00 | 1 |
| 2 | Mouse | 50.00 | 2 |
| 3 | Laptop | 800.00 | 1 |
| 3 | Smartwatch | 150.00 | 2 |
在这个例子中，使用 ROW_NUMBER() 按照每个 order_id 对商品按 unit_price 从高到低排序，并为每个商品分配了一个行号（排名）。
如果只想获取每个订单中价格最高的商品，可以在查询外层再加一个 WHERE rank = 1 来筛选。
示例 2：去除重复数据
假设 order_items 表中有重复的记录，可以利用 ROW_NUMBER() 给每一行编号，然后只保留每组中第一个出现的记录（行号为 1）。
插入重复数据
INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00), -- 重复记录
(2, 'Mouse', 1, 50.00), -- 重复记录
(3, 'Smartwatch', 1, 150.00);
查询：去除重复记录
WITH ranked_items AS (SELECT oi.order_item_id, oi.order_id, oi.product_name, oi.unit_price,ROW_NUMBER() OVER (PARTITION BY oi.order_id, oi.product_name ORDER BY oi.order_item_id) AS rnFROM order_items oi
)
SELECT order_item_id, order_id, product_name, unit_price
FROM ranked_items
WHERE rn = 1;
| order_item_id | order_id | product_name | unit_price |
|---|---|---|---|
| 1 | 1 | Laptop | 1000.00 |
| 2 | 1 | Phone | 500.00 |
| 3 | 1 | Tablet | 300.00 |
| 4 | 2 | Headphones | 100.00 |
| 5 | 2 | Mouse | 50.00 |
| 7 | 3 | Laptop | 800.00 |
| 6 | 3 | Smartwatch | 150.00 |
在这个查询中，ROW_NUMBER() 根据 order_id 和 product_name 为每一组商品打上编号，PARTITION BY 确保每个订单中同一个商品只保留一次。WHERE rn = 1 确保每个分组只保留第一条记录，从而去除了重复的商品条目。
示例 3：分页查询
假设需要分页展示订单项，每页展示 2 条数据。可以使用 ROW_NUMBER() 来为查询结果生成行号，并结合 WHERE 子句限制显示特定页的数据。
查询：分页显示第二页数据（每页显示 2 条）
WITH ranked_items AS (SELECT oi.order_item_id, oi.order_id, oi.product_name, oi.unit_price,ROW_NUMBER() OVER (ORDER BY oi.order_item_id) AS rnFROM order_items oi
)
SELECT order_item_id, order_id, product_name, unit_price
FROM ranked_items
WHERE rn BETWEEN 3 AND 4;
结果
| order_item_id | order_id | product_name | unit_price |
|---|---|---|---|
| 3 | 1 | Tablet | 300.00 |
| 4 | 2 | Headphones | 100.00 |
在这个分页查询中，ROW_NUMBER() 为查询结果集中的每一行分配了一个行号，然后通过 WHERE rn BETWEEN 3 AND 4 获取第 2 页的结果（假设每页 2 条数据）。
总结
ROW_NUMBER() 在 MySQL 中是一个强大的窗口函数，具有以下几个主要用途：
- 分页查询：通过生成行号来实现高效分页。
- 去重：利用分组和行号，可以去除重复数据。
- 分组排序：对每个分组内的数据进行排序并生成排名。
- 数据排名：计算排名或为数据按某种规则分配顺序。
MySQL 8.0 引入的窗口函数使得许多复杂的查询变得更加简洁和高效，特别是在处理排名、去重和分页等场景时。
关于作者
来自全栈程序员nine的探索与实践，持续迭代中。（技术交流codetrend）