MySQL / 系统课时
窗口函数与结果集合并
窗口函数与结果集合并
窗口函数解决“保留明细行,同时计算排名、序号或分组统计”的问题;UNION 则用于把多个结构兼容的查询结果纵向合并。掌握两者后,可以完成组内排名、分页编号以及跨条件汇总等常见高级查询。
学习目标
- 使用
ROW_NUMBER()为结果集或分组内记录编号 - 理解窗口函数与
GROUP BY聚合的区别 - 正确使用
UNION和UNION ALL合并查询结果
用 ROW_NUMBER 进行组内编号
窗口函数不会像 GROUP BY 那样把多行压缩成一行,而是在保留原始记录的基础上计算额外列。ROW_NUMBER() 常用于排序编号和组内排名。
假设 orders 表包含 id、user_id、amount 和 created_at,下面为每个用户的订单按时间从新到旧编号。
SELECT
id,
user_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC, id DESC
) AS rn
FROM orders
ORDER BY user_id, rn;预期结果
+----+---------+--------+----+
| id | user_id | amount | rn |
+----+---------+--------+----+
| 12 | 101 | 320.00 | 1 |
| 8 | 101 | 180.00 | 2 |
| 15 | 102 | 500.00 | 1 |
| 9 | 102 | 260.00 | 2 |
+----+---------+--------+----+实际编号取决于数据。PARTITION BY user_id 表示每个用户单独形成一个窗口,ORDER BY 决定窗口内部编号顺序。加入 id DESC 可以在创建时间相同时提供稳定的次级排序条件。
从每组中取第一条记录
窗口函数的常见用途是“每组取最新一条”。因为同一层查询通常不能直接在 WHERE 中引用刚计算出的窗口函数结果,可以先通过 CTE 计算编号,再在外层筛选。
WITH ranked_orders AS (
SELECT
id,
user_id,
amount,
created_at,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC, id DESC
) AS rn
FROM orders
)
SELECT id, user_id, amount, created_at
FROM ranked_orders
WHERE rn = 1
ORDER BY user_id;预期结果
+----+---------+--------+---------------------+
| id | user_id | amount | created_at |
+----+---------+--------+---------------------+
| 12 | 101 | 320.00 | <TIME> |
| 15 | 102 | 500.00 | <TIME> |
+----+---------+--------+---------------------+<TIME> 代表实际订单时间。与 GROUP BY user_id 不同,这种写法能够返回最新订单对应的完整明细,而不只是一个最大时间或聚合值。
用 UNION 合并结果集
UNION 用于把多个 SELECT 的结果按行向下拼接。各查询返回的列数必须相同,对应位置的数据类型也应兼容,最终列名通常由第一个查询决定。
下面把高金额订单和指定用户的订单合并为一个结果集。
SELECT id, user_id, amount
FROM orders
WHERE amount >= 1000
UNION
SELECT id, user_id, amount
FROM orders
WHERE user_id = 101
ORDER BY id;预期结果
+----+---------+---------+
| id | user_id | amount |
+----+---------+---------+
| 3 | 101 | 150.00 |
| 7 | 103 | 1200.00 |
| 12 | 101 | 320.00 |
+----+---------+---------+如果同一行同时满足两个查询条件,UNION 会对最终结果去重。若业务允许重复,使用 UNION ALL 可以直接保留所有记录,也避免额外的去重处理。
UNION 和 UNION ALL 的选择
当你明确需要集合语义,也就是重复行只能出现一次时,使用 UNION。如果两个结果来源本身不会重叠,或者重复记录具有业务意义,应优先考虑 UNION ALL。
窗口函数与结果集合并解决的是不同方向的问题:窗口函数在一个结果集中增加基于分区和排序的计算,UNION 则把多个查询结果纵向拼接。需要“组内排名”时考虑窗口函数,需要“把多个查询结果放到一起”时考虑 UNION 或 UNION ALL。