PostgreSQL 10中如何按被连接表指定顺序执行JOIN操作

这个问题我之前也碰到过，PostgreSQL的DISTINCT ON确实是处理分组取首行的实用工具，但它的排序逻辑有个容易踩的坑——必须把DISTINCT ON指定的列放在ORDER BY的最前面，否则你给关联表指定的排序规则根本不会作用到分组后的选择上。

问题根源拆解 #

你遇到的情况是：当单日存在多笔金额匹配的发票时，DISTINCT ON (cap.slipNr)虽然能去掉重复的单据记录，但因为你没正确设置ORDER BY的优先级，PostgreSQL会用默认的无序逻辑来选择保留哪一行发票，自然不符合你的预期。

解决方案：正确组合DISTINCT ON与ORDER BY #

PostgreSQL的DISTINCT ON规则是：会为DISTINCT ON (列)的每个分组，保留ORDER BY排序后的第一行。所以你的ORDER BY必须遵循「先分组列，后排序规则」的顺序。

举个实际的例子，假设你的表结构如下：

• 信用卡单据表creditcard_slips（简称cap）：slipNr（单据号）、trans_date（交易日期）、amount（交易金额）
• 发票表invoices（简称inv）：invoice_id（发票号）、invoice_date（发票日期）、amount（发票金额）、created_at（发票创建时间）

错误的查询（你可能正在用的）

这种写法只按发票字段排序，没把分组列cap.slipNr放在ORDER BY首位，导致分组后的发票选择逻辑混乱：

SELECT DISTINCT ON (cap.slipNr)
  cap.slipNr, cap.trans_date, cap.amount,
  inv.invoice_id, inv.invoice_date
FROM creditcard_slips cap
JOIN invoices inv ON cap.trans_date = inv.invoice_date AND cap.amount = inv.amount
ORDER BY inv.created_at DESC;

正确的查询

把cap.slipNr放在ORDER BY的第一位，再跟你想要的发票排序规则（比如按创建时间倒序选最新发票，或按发票号升序选最早发票）：

SELECT DISTINCT ON (cap.slipNr)
  cap.slipNr, cap.trans_date, cap.amount,
  inv.invoice_id, inv.invoice_date, inv.created_at
FROM creditcard_slips cap
JOIN invoices inv ON cap.trans_date = inv.invoice_date AND cap.amount = inv.amount
-- 先按单据号分组，再按发票创建时间倒序，确保每个单据匹配最新的同条件发票
ORDER BY cap.slipNr, inv.created_at DESC;

关键说明 #

• 如果你需要按其他业务规则选择发票（比如优先匹配编号最小的发票），只需要把inv.created_at DESC换成inv.invoice_id ASC即可。
• 核心逻辑永远是：ORDER BY必须以DISTINCT ON指定的列开头，这样分组后的排序规则才会生效，确保你拿到的是符合预期的那笔发票。

内容的提问来源于stack exchange，提问作者leu