如果对多个列应用排序依据，则不对计算列进行排序 [英] Order by not working on calculated columns if order by applied on multiple columns

问题描述

我正在使用PostgreSQL版本9.6。我想在具有一个现有列的计算列上应用订单。如果仅在计算列上应用订单，则可以正常工作，但是在order by子句中再添加一列时，则会抛出错误。

I am using postgresql version 9.6. I want to apply order by on a calculated column with one existing column. If I apply order only on calculated column then it working fine but it is throwing error when one more column is added in order by clause.

错误是：

ERROR:  column "column_name" does not exist

以下是查询：

创建表：

CREATE TABLE "student" (
    "age" numeric(2) NOT NULL ,
    "name" varchar(128) NOT NULL);

插入数据：

insert into student values(22, 'Vikram');
insert into student values(12, 'Bhagirath');
insert into student values(12, 'SKR');

查询：

Select *, 
       CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student 
order by category;

上面的查询工作正常。如果我在子句中添加 name 则抛出错误：

The above query is working fine. If I add name to order by clause then it throw error:

Select *, 
       CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student order by (category,name);

错误：

ERROR:  column "category" does not exist

推荐答案

通过删除组的列周围的括号：

Remove those parentheses around the columns for the group by:

Select *, CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student 
order by category, name;

在线示例： https://rextester.com/BHQ36816

这篇关于如果对多个列应用排序依据，则不对计算列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！