如何解决SQL中DISTINCT、ORDER BY与CASE协同使用的报错问题？

问题背景 #

需求排序规则：若numbers.is_exclusive为TRUE，则按fees.exclusive_price排序，否则按fees.additional_price排序。

原执行的SQL语句：

SELECT DISTINCT "numbers".* 
FROM "numbers" 
INNER JOIN "users_numbers" ON "users_numbers"."number_id" = "numbers"."id" 
INNER JOIN "users" ON "users"."id" = "users_numbers"."user_id" 
INNER JOIN "fees" ON "fees"."user_id" = "users"."id" 
WHERE "numbers"."state" != 'removed' 
ORDER BY CASE "numbers"."is_exclusive" WHEN TRUE THEN "fees"."exclusive_price" ELSE "fees"."additional_price" END desc

在Rails中触发报错：

ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block

排查后发现：移除DISTINCT语句可正常执行；尝试在SELECT中添加fees的字段也无法解决问题：

SELECT DISTINCT "fees"."exclusive_price", "fees"."additional_price", "numbers".*
FROM ...

问题原因 #

PostgreSQL对DISTINCT查询的ORDER BY有严格限制：排序字段必须属于SELECT的返回列表，或者是能被DISTINCT逻辑覆盖的聚合结果。原SQL中排序依赖fees表的字段，但SELECT只返回numbers.*，即使添加fees字段，若一个number对应多条fees记录，DISTINCT会保留多条不同价格的记录，无法实现仅去重numbers的需求。

修复方案 #

方案1：子查询预计算排序值，再关联去重 #

先通过子查询为每个number算出对应的排序价格，再关联主表做去重和排序：

SELECT DISTINCT n.*
FROM "numbers" n
INNER JOIN (
  SELECT 
    un.number_id,
    CASE n_inner.is_exclusive WHEN TRUE THEN f.exclusive_price ELSE f.additional_price END AS sort_price
  FROM "numbers" n_inner
  INNER JOIN "users_numbers" un ON un.number_id = n_inner.id
  INNER JOIN "users" u ON u.id = un.user_id
  INNER JOIN "fees" f ON f.user_id = u.id
  WHERE n_inner.state != 'removed'
) sorted ON sorted.number_id = n.id
WHERE n.state != 'removed'
ORDER BY sorted.sort_price DESC

方案2：用GROUP BY替代DISTINCT（需numbers.id为主键） #

利用GROUP BY numbers.id实现去重（PostgreSQL中主键分组可直接返回整行字段），同时用聚合函数处理排序字段（避免同一number对应多条fees记录的歧义）：

SELECT "numbers".*
FROM "numbers"
INNER JOIN "users_numbers" ON "users_numbers"."number_id" = "numbers"."id"
INNER JOIN "users" ON "users"."id" = "users_numbers"."user_id"
INNER JOIN "fees" ON "fees"."user_id" = "users"."id"
WHERE "numbers"."state" != 'removed'
GROUP BY "numbers".id
ORDER BY MAX(CASE "numbers"."is_exclusive" WHEN TRUE THEN "fees"."exclusive_price" ELSE "fees"."additional_price" END) DESC

注：这里用MAX()取同一number对应价格的最大值作为排序依据，可根据业务需求替换为MIN()或其他聚合函数。

方案3：窗口函数去重并排序 #

使用ROW_NUMBER()窗口函数按number分组，按目标规则排序后取每组第一条记录：

SELECT *
FROM (
  SELECT 
    n.*,
    ROW_NUMBER() OVER (
      PARTITION BY n.id 
      ORDER BY CASE n.is_exclusive WHEN TRUE THEN f.exclusive_price ELSE f.additional_price END DESC
    ) AS rn
  FROM "numbers" n
  INNER JOIN "users_numbers" un ON un.number_id = n.id
  INNER JOIN "users" u ON u.id = un.user_id
  INNER JOIN "fees" f ON f.user_id = u.id
  WHERE n.state != 'removed'
) numbered
WHERE rn = 1
ORDER BY CASE is_exclusive WHEN TRUE THEN exclusive_price ELSE additional_price END DESC

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