sql - 如何在 PostgreSQL ORDER BY 子句中使用 ALIAS？

Question

我有以下查询:

SELECT 
    title, 
    (stock_one + stock_two) AS global_stock
FROM
    product
ORDER BY
    global_stock = 0,
    title;

在 PostgreSQL 8.1.23 中运行它时出现此错误:

查询失败:错误:列“global_stock”不存在

有人可以帮我让它工作吗？我首先需要可用的项目，然后是不可用的项目。非常感谢!

Answer 1

您始终可以这样ORDER BY:

select 
    title, 
    ( stock_one + stock_two ) as global_stock
from product
order by 2, 1

或将其包装在另一个 SELECT 中:

SELECT *
from
(
    select 
        title, 
        ( stock_one + stock_two ) as global_stock
    from product
) x
order by (case when global_stock = 0 then 1 else 0 end) desc, title