示例
示例
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY o.user_id
ORDER BY o.created_at DESC, o.id DESC) AS rn
FROM orders o
)
SELECT u.id, u.name, r.amount AS last_amount
FROM users u
LEFT JOIN ranked r
ON r.user_id = u.id AND r.rn = 1
ORDER BY u.id;| 思路 | 组内排序编号，再筛编号 1 |
|---|---|
| 连接 | LEFT JOIN 保留没有订单的用户 |
| 索引 | PARTITION BY + ORDER BY 列上的复合索引可减少排序代价 |