ROW_NUMBER()通过PARTITION BY分组编号、ORDER BY确定顺序、ON中筛选rn=1实现精准压缩,可避免JOIN一对多导致的主表记录膨胀,但需确保排序字段有索引且数据库版本支持。

用 ROW_NUMBER() 窗口函数过滤重复主表记录
直接 JOIN 一对多关系时,主表一行会因从表多行而膨胀成多行。想“只取第一条”,本质是给从表每组匹配记录编号,再筛出 rn = 1 的那条。这是最通用、语义最清晰的做法。
常见错误是误用 GROUP BY + 聚合函数(如 MAX()),这会丢失整行结构,且无法保证取到的是逻辑上“第一条”(比如按时间或ID排序后的首条)。
- 必须搭配
ORDER BY子句在ROW_NUMBER()内明确排序依据,否则“第一条”无定义 - 分区键(
PARTITION BY)要设为关联字段,例如PARTITION BY t1.id,确保每个主表记录独立编号 - MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持;SQLite 3.25+ 也支持
SELECT t1.id, t1.name, t2.order_id, t2.amount
FROM orders t1
LEFT JOIN (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn
FROM order_items
) t2 ON t1.id = t2.order_id AND t2.rn = 1;
用 LATERAL / APPLY 实现每行独立子查询
当需要更灵活的“第一条”逻辑(比如带复杂条件、或依赖主表字段计算),LATERAL(PostgreSQL、SQL Server 的 APPLY)比窗口函数更直接:对主表每一行,执行一次子查询并 LIMIT 1。
优势是子查询可引用主表字段,且天然避免全量关联再过滤的开销;劣势是语法不跨数据库,MySQL 不原生支持(需改写为相关子查询或变量模拟)。
- PostgreSQL 示例:
LATERAL (SELECT * FROM order_items WHERE order_items.order_id = t1.id ORDER BY created_at DESC LIMIT 1) - SQL Server 用
CROSS APPLY,语义相同 - 注意
LIMIT 1或TOP 1必须配合ORDER BY,否则结果不确定
警惕 DISTINCT 和 GROUP BY 的误导性“去重”
DISTINCT 只能去掉完全相同的行,如果从表字段不同(比如多个订单项的 item_name 不同),它无法保证“每个主表只留一条从表数据”。GROUP BY 更危险——它强制聚合,若漏写 ANY_VALUE()(MySQL)或非聚合字段,会报错或返回不可靠值。
- 不要写
SELECT DISTINCT t1.*, t2.* FROM ...试图“去重”,无效 - 避免
GROUP BY t1.id后直接选t2.item_name,MySQL 5.7+ 默认 SQL mode 下会报错,放宽模式后行为未定义 - 真要用
GROUP BY,必须所有从表字段都套聚合函数,如MAX(t2.item_name),但这不是“取第一条”,而是取字典序最大值
性能关键点:索引必须覆盖 JOIN 和 ORDER BY 字段
无论用窗口函数还是 LATERAL,数据库都要快速定位每个主表 ID 对应的从表记录,并按指定字段排序。没索引时,性能会断崖式下跌。
- 从表上建复合索引:例如
CREATE INDEX idx_order_items_order_time ON order_items (order_id, created_at DESC); - 如果排序字段常变化(比如有时按
created_at,有时按id),考虑建多个覆盖索引,或权衡是否值得 - EXPLAIN 查看执行计划,确认是否走了索引扫描而非全表扫描
真正难的不是写法,是明确“第一条”的业务定义(按时间?按ID?按状态优先级?),以及确保索引能让这个定义高效落地。










