PostgreSQL如何结合COALESCE使用DISTINCT ON取最新记录求和

PostgreSQL的DISTINCT ON语法有强制规则：ORDER BY子句的前导表达式必须和DISTINCT ON()内指定的去重表达式完全一致。你原查询仅按creation_time倒序，没有先按合并后的ID排序，导致去重逻辑无法按预期取到每个合并ID下的最新记录。

SELECT COALESCE(SUM(ct.amount), 0) AS total_amount
FROM (
  SELECT
    DISTINCT ON (COALESCE(NULLIF(pt.originalID, 0), pt.ID))
    pt.amount
  FROM prettyTable AS pt
  -- 此处替换为你实际需要的WHERE筛选条件
  -- WHERE ...
  ORDER BY
    -- 必须先写DISTINCT ON对应的去重表达式，再按创建时间倒序取最新条目
    COALESCE(NULLIF(pt.originalID, 0), pt.ID),
    pt.creation_time DESC
) AS ct;

针对你给出的测试数据，查询执行逻辑如下：

• 逐行计算合并ID：originalID非0时取originalID，否则取ID
• 对每个合并ID分组，按creation_time倒序取第一条：
  • 合并ID=1：仅1条记录，amount=10
  • 合并ID=2：共3条记录，取2004-10-22最新条目，amount=30
  • 合并ID=5：共2条记录，取2004-10-23最新条目，amount=10
• 求和结果为10+30+10=50，完全符合预期。

如果需要在不支持DISTINCT ON的数据库（如MySQL、SQL Server）中实现相同逻辑，可以改用窗口函数写法：

SELECT COALESCE(SUM(t.amount), 0) AS total_amount
FROM (
  SELECT
    pt.amount,
    ROW_NUMBER() OVER (
      PARTITION BY COALESCE(NULLIF(pt.originalID, 0), pt.ID)
      ORDER BY pt.creation_time DESC
    ) AS row_num
  FROM prettyTable AS pt
  -- 此处替换为你实际需要的WHERE筛选条件
  -- WHERE ...
) AS t
WHERE t.row_num = 1;

• 如果存在同一合并ID下多条记录creation_time完全一致的场景，可以在ORDER BY末尾追加主键字段（如pt.ID DESC）作为兜底排序，避免返回结果不确定
• 不建议在子查询中给DISTINCT ON的表达式起别名后直接在ORDER BY中引用，部分PostgreSQL版本会出现识别异常，直接写完整表达式兼容性最好

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