postgresql - 按作为串联的一部分选择的字段排序 - PostgreSQL

Question

我想按作为连接一部分的字段进行排序。

例如:

SELECT DISTINCT(field1 ||' '||field2) AS field3 from Table ORDER BY field1;

是否可以通过其他方式做到这一点？我收到一条错误消息，指出我无法按未选择的字段进行排序。

有没有办法选择一个字段但使其不可见，以便我可以按它进行排序？

Answer 1

这可以使用子查询或 CTE 来完成:

子查询:

SELECT c.field3
FROM (SELECT DISTINCT(field1 ||' '||field2) AS field3 from Table) c
ORDER BY c.field3;

CTE:

WITH ConcatTable AS (
  SELECT DISTINCT(field1 ||' '||field2) AS field3 from Table
) 
SELECT field3 FROM ConcatTable ORDER BY field3;

也可以通过简单地重复所选表达式来完成，但我不喜欢这样做，因为代码重复:

SELECT DISTINCT(field1 ||' '||field2)
FROM Table
ORDER BY field1 ||' '||field2;

======编辑=========

正如评论中所指出的，按串联字段排序与仅按字段 1 排序并不完全相同。如果您真的只想按 field1 排序，则必须执行以下操作:

子查询:

SELECT c.field3
FROM (SELECT DISTINCT field1, (field1 ||' '||field2) AS field3 from Table) c
ORDER BY c.field1;

CTE:

WITH ConcatTable AS (
  SELECT DISTINCT field1, (field1 ||' '||field2) AS field3 from Table
) 
SELECT field3 FROM ConcatTable ORDER BY field1;