主页 / dba / 问题 / 165042
Accepted
Dave
Asked: 2017-02-22 08:04:52 +0800 CST

如何基于列的串联构建 PostgreSQL ORDER BY 表达式？

  • 772

我正在使用 Postgres 9.5。我想为一系列连接的字符串编写一个表达式顺序，但我不确定这是否可能或如何去做。我拼凑了下面

myproject=> SELECT  "my_objects".* 
            FROM "my_objects" 
              left join addresses on my_objects.address_id = addresses.id 
            WHERE (my_objects.name ILIKE '%my_object%' 
              AND EXISTS (SELECT * 
                          FROM my_object_times 
                          WHERE my_object_times.my_object_id = my_objects.id)) 
            order by lower(addresses.city) || "," || addresses.state_id; 

ERROR:  column "," does not exist 
LINE 1: ...id = my_objects.id)) order by lower(addresses.city) || "," || add...

但是正如您所看到的，有一个抱怨语法的错误。我正在尝试做的事情是否可行，如果可以，我如何根据列的串联编写 order by 子句？

postgresql order-by

1 个回答

  1. Best Answer

    在这里我可以说两点，

    1. 解决方案：","与','单引号不同的是字面引用机制。双引号表示命名的列,完全有效，但在实践中会很糟糕CREATE TABLE foo ("," int);

      -- Identifier quoting
order by lower(addresses.city) || "," || addresses.state_id;
-- Literal quoting
order by lower(addresses.city) || ',' || addresses.state_id;

    2. 更大的问题：为什么要连接字符串？它只会减慢速度或失去准确性。您可以按多列排序；就这样做..

      order by lower(addresses.city), addresses.state_id;
    • 5

相关问题