如何使用DISTINCT ON去重同时按其他字段完成排序？

方案1：保留DISTINCT ON，外层嵌套排序（最省改动） #

你原来的查询逻辑已经正确筛选出了每个Subscription对应最早authorized_at的关联购物车记录，报错是因为PostgreSQL强制要求 DISTINCT ON 的表达式必须和ORDER BY的第一个排序项完全一致，你只需要把现有查询作为子查询，外层再加排序即可：

SELECT * FROM (
  select distinct on (s.id) s.id as subscription_id, subscription_carts.authorized_at, s.*
  from subscriptions s
  join subscription_carts subscription_carts on subscription_carts.subscription_id = s.id 
  and subscription_carts.plan_id = s.plan_id
  where subscription_carts.status = 'processed'
  and s.status IN ('authorized','in_trial', 'paused')
  order by s.id, subscription_carts.authorized_at
) t
ORDER BY authorized_at; -- 此处按需求调整升序/降序即可

这个方案不需要修改你已经验证过的核心逻辑，改动成本最低，性能也没有明显损耗。

方案2：改用GROUP BY实现（兼容非PostgreSQL场景） #

如果不想用PG特有的DISTINCT ON语法，也可以用分组配合聚合函数实现，如果你只需要拿到authorized_at字段，写法非常简单：

select s.id as subscription_id, min(subscription_carts.authorized_at) as authorized_at, s.*
from subscriptions s
join subscription_carts subscription_carts on subscription_carts.subscription_id = s.id 
and subscription_carts.plan_id = s.plan_id
where subscription_carts.status = 'processed'
and s.status IN ('authorized','in_trial', 'paused')
group by s.id -- PostgreSQL支持只要主键在GROUP BY里就可以取表所有字段
order by min(subscription_carts.authorized_at);

如果你还需要获取最早authorized_at对应的SubscriptionCart其他字段，可以配合窗口函数实现：

WITH ranked_carts AS (
  select 
    s.id as subscription_id, 
    sc.*,
    s.*,
    ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY sc.authorized_at) as rn
  from subscriptions s
  join subscription_carts sc on sc.subscription_id = s.id 
  and sc.plan_id = s.plan_id
  where sc.status = 'processed'
  and s.status IN ('authorized','in_trial', 'paused')
)
SELECT * FROM ranked_carts WHERE rn = 1
ORDER BY authorized_at;

方案对比 #

• 你原来的DISTINCT ON方案是PG下性能较优的实现，完全没必要替换，套一层外层排序是成本最低的选择
• GROUP BY/窗口函数的方案兼容性更好，适合需要跨数据库适配的场景

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