MariaDB 教程 / 用 ROW_NUMBER、RANK 和 DENSE_RANK 计算序号与排名
窗口函数在保留输入结果行的同时,按一组有序记录添加计算。ROW_NUMBER 为每行生成不同序号,RANK 与 DENSE_RANK 则可以让相同排序值共享名次;先决定并列规则,再选择函数。
区分唯一行号与共享名次
客户购买金额相同时,展示列表可能仍需要不同的行号,但金额排名应允许并列。应先为每位客户汇总一笔金额,再对这些客户行计算排名。只更换函数名,不能修正输入本来还是订单明细、或者并列定义不正确的问题。
| 函数 | 相同排序值 | 本例序列 |
|---|---|---|
| ROW_NUMBER | 每行仍得到不同序号 | 1、2、3、4 |
| RANK | 共享名次,后面的名次会跳号 | 1、1、3、3 |
| DENSE_RANK | 共享名次,后面不跳号 | 1、1、2、2 |
使用 WITH 与 CTE可以把客户汇总与排名分成不同阶段。相关函数规则见官方 ROW_NUMBER、RANK、DENSE_RANK与 窗口函数概览。
准备购买金额刻意相同的客户样本
以 course_user 连接学习服务器,先核对 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c32_customers、c32_products、c32_orders、c32_items;如果有同名对象,先检查结构和记录,不要覆盖用途未明的数据。
以下是完整准备代码,应在 MariaDB 客户端中执行。也可按 SQL 文件与 SOURCE 教程保存 UTF-8 文件并使用实际路径加载。确认服务器支持这里的窗口函数写法,不要把 SQL 输入操作系统命令行。
USE shopcourse;
CREATE TABLE c32_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c32_order_customer FOREIGN KEY (customer_id)
REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c32_item_order FOREIGN KEY (order_id)
REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c32_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
原始韩文姓名、城市与商品字符串保持不变。商品 1~4 分别是笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市为首尔,2 为釜山,4 为大田。比较结果时使用编号,避免翻译字面量改变样本。
客户 1、2 的已支付商品金额都为 19500.00。客户 3 只有取消订单,客户 4 完全没有订单;本报表明确把这两位的已支付金额设为零。是否包括未购买者、是否排除取消订单,都是排名总体的定义,不是函数自动替您作出的决定。
先汇总客户金额,再比较三个函数
WITH customer_totals AS (
SELECT c.customer_id,c.customer_name,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c LEFT JOIN c32_orders o
ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;
| 客户编号 | 金额 | row_no | amount_rank | dense_amount_rank |
|---|---|---|---|---|
| 1 | 19500.00 | 1 | 1 | 1 |
| 2 | 19500.00 | 2 | 1 | 1 |
| 3 | 0.00 | 3 | 3 | 2 |
| 4 | 0.00 | 4 | 3 | 2 |
ROW_NUMBER 的窗口排序除了金额,还加入唯一的 customer_id,让同金额客户之间的顺序有明确规则。两个排名函数则只按金额排序,因而保留金额相同的并列关系。给 RANK 和 DENSE_RANK 也加上唯一客户编号,会使完整排序组合不同,并列就消失了。
RANK 中前两位占据两个位置,下一组从 3 开始;DENSE_RANK 只把不同金额分成第 1、2 组,所以不跳号。判断哪个正确,取决于报表要表达竞赛名次、不同金额档位,还是逐行序号。
只把 ROW_NUMBER 内的客户编号方向从 ASC 改成 DESC,同金额内的行号会反转,但按金额计算的两个名次不应改变。核对后恢复 ASC,确认结果回到表中的顺序。排名是计算结果,不应代替稳定的客户主键。
区分窗口顺序、最终显示顺序与分区
OVER 内的 ORDER BY 决定函数怎样计算,最终 SELECT 的 ORDER BY 决定怎样显示结果。如果两者都重要,应分别写明。只改变最终显示为客户编号降序,可以看到输出顺序改变,但原来计算出的 row_no 仍与对应客户保持一致。
PARTITION BY 决定计算在哪些组内重新开始。它不像 GROUP BY 那样把客户行折叠成一行,而是在保留每位客户的同时,分别为各城市计算序号。以下完整查询把 city 加入已聚合输入,仍使用本节四表:
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
ROW_NUMBER() OVER (PARTITION BY city
ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;
| customer_id | goods_total | regional_row_no |
|---|---|---|
| 1 | 19500.00 | 1 |
| 2 | 19500.00 | 1 |
| 3 | 0.00 | 2 |
| 4 | 0.00 | 1 |
客户 1、3 同在首尔分区,序号分别为 1、2。客户 2、4 各自在另一个城市分区中从 1 开始。只移除 PARTITION BY city,就变成全局序号 1、2、3、4;比较后恢复城市分区,确认各城市重新计数。
在外层查询筛选已经计算的排名
同一个 SELECT 的 WHERE 不能直接筛选刚生成的 row_no。把排名结果放进 CTE 或派生表,再由外层 WHERE 检查计算列。这样也能把“客户金额汇总”“排名”“取目标行”三个阶段分别核对。
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;
结果为客户 1、2,行号分别 1、2,金额名次都为 1。只把外层条件改成 row_no <= 1,得到一行;改为 amount_rank = 1,则保留两位并列第一。比较后恢复 row_no <= 2。
“取两行”“取前两个不同金额档位”和“所有并列第一”是不同需求。应选择对应的计算字段与边界,不要用一个看似叫作排名的列替代所有问题。若先筛掉某些客户再排名,还可能改变参与排名的总体。
同样,直接对原始六条商品明细计算 ROW_NUMBER,会得到明细行序号,而不是四位客户的序号。窗口函数保留的是它实际收到的输入结果行,不能自动判断业务上想排名的对象。
用并列值验证规则与统计人群
测试时应刻意保留相同金额,并检查并列之后的名次。核对客户编号的次级排序只影响唯一行号,金额排名仍按预期共享;再分别检查分区、最终显示顺序与外层过滤,避免同时改动多个条件后无法定位差异。
还应确认取消金额没有进入排名,未购买者是否应被保留为零,以及每位客户的金额是否已经正确汇总。基础聚合有误时,窗口函数只会给错误金额排出一个看起来整齐的顺序。CASE 条件聚合教程解释了本例的状态选择方式。
这些查询不修改原始四表。清理前检查定义与记录,备份应保留的数据,再只删除已确认可丢弃的本节表。下面按子表到父表的顺序执行;DROP TABLE 会删除定义和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;




