如何在PostgreSQL中用SQL的DISTINCT ON提取最新记录-SQL-PHP中文网

distinct on 必须配合 order by 使用，且 order by 首字段须与 distinct on 列一致；where 应置于 order by 前；需为排序列建联合索引；复杂场景建议用 row_number() 替代。

如何在postgresql中用sql的distinct on提取最新记录

DISTINCT ON 语法必须配合 ORDER BY 使用

PostgreSQL 的 DISTINCT ON 不是独立去重机制，它只在明确指定排序后才可靠提取“每组第一条”。如果你只写 SELECT DISTINCT ON (user_id) * FROM orders 而不加 ORDER BY，结果不可预测——PostgreSQL 允许执行，但返回哪条记录完全取决于当前查询计划和数据物理分布，不是按时间、ID 或任何业务逻辑。

正确做法是把排序字段放在 ORDER BY 的最前面，且顺序必须与 DISTINCT ON 的列一致（或至少前缀一致）：

SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

user_id 必须出现在 ORDER BY 开头，否则报错 SELECT DISTINCT ON expressions must match initial ORDER BY expressions
created_at DESC 确保每组内最新记录排在最前，被 DISTINCT ON 拿到
如果想取最早记录，改成 created_at ASC

WHERE 条件要放在 ORDER BY 之前，不能放后面

很多人误以为可以在 ORDER BY 后加 WHERE，或者把过滤条件挪到子查询外层——这会破坏 DISTINCT ON 的语义。WHERE 必须在主查询中直接作用于源表，否则可能漏掉本该参与分组的行，或让排序失去依据。

错误示例（WHERE 放错位置）：

-- ❌ 错误：WHERE 在子查询外，DISTINCT ON 已经执行完毕
(SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC)
WHERE status = 'completed';

正确写法是把过滤提前：

SELECT DISTINCT ON (user_id) *
FROM orders
WHERE status = 'completed'
ORDER BY user_id, created_at DESC;

如果 WHERE 过滤后某 user_id 没有记录，那它就不会出现在结果里
不要试图用 HAVING 替代 WHERE —— DISTINCT ON 不是聚合操作，不支持 HAVING

性能关键：给 DISTINCT ON + ORDER BY 的组合列建联合索引

没有索引时，DISTINCT ON (a) ... ORDER BY a, b DESC 会触发全表扫描 + 排序，数据量稍大就明显变慢。PostgreSQL 无法利用单列索引优化这个场景。

必须创建覆盖全部排序字段的联合索引：

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

索引列顺序必须严格匹配 ORDER BY：先是 DISTINCT ON 列，再是排序列
如果 ORDER BY 是 user_id, created_at DESC, id DESC，索引也得写成 (user_id, created_at DESC, id DESC)
用 EXPLAIN 验证是否走了索引扫描（看到 Index Scan using ... 而非 Sort + Unique）

替代方案：用窗口函数 ROW_NUMBER() 更灵活但更重

当需要同时提取“最新”和“次新”，或分组依据动态变化（比如按日期区间分组），DISTINCT ON 就不够用了。这时改用 ROW_NUMBER() 更可控：

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
  WHERE status = 'completed'
) ranked
WHERE rn = 1;