postgresql - ORDER BY PostgreSQL 中的复杂表达式

Question

我正在尝试按 2 个 double 值(别名列)之间的差异进行排序，但它看不到别名列。

例子:

SELECT COALESCE(
  ROUND(
   SUM(amount * currency1.rate / currency2.rate)
   , 4)
 , 0) AS first_amount,
SUM(
   (SELECT
     COALESCE(
       ROUND(
         SUM(table2.amount * currency3.rate / currency2.rate)
       , 4)
     , 0)
   FROM table2
     JOIN currencies currency3
       ON currency3.id = table2.currency_id
   WHERE table2.date BETWEEN table1.start_date AND table1.end_date
   )
 ) AS second_amount
 FROM table1
 JOIN currencies currency1
   ON currency3.id = table1.currency_id
 JOIN currencies currency2
   ON currency3.id = 123  # some hardcoded ID
 ORDER BY first_amount - second_amount ASC

Postgres 告诉我列 first_amount 不存在。阅读文档，我看到 Postgres 9.0 不允许带有别名列的表达式。

如何通过以正确的方式对我需要的所有东西进行排序来解决问题？

Answer 1

不能在 where 或 order by 子句中直接使用列别名。您需要将其包装在派生表中。

select *
from (
  ... your original query goes here ...
) as t
ORDER BY first_amount - second_amount ASC;