如何在PostgreSQL ORDER BY子句中使用别名？

Question

我有以下疑问：

select 
    title, 
    ( stock_one + stock_two ) as global_stock

from product

order by
    global_stock = 0,
    title;

在PostgreSQL 8.1.23中运行时，出现以下错误：

查询失败：错误：“全局库存”列不存在

有人能帮我把它投入工作吗？我首先需要可用的项目，然后是不可用的项目。非常感谢！

Answer 1

你总是可以这样ORDER BY：

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

或者将其包装在另一个选择中：

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

Answer 2

一种解决方案是使用以下位置：

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

但是，别名应该可以工作，但不一定是表达式。“全球股票=0”是什么意思？你的意思是说：

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