PostgreSQL: 按列排序，将特定的非空值排在最后

Question

当我发现NULLS LAST时，我有点希望它可以推广到查询的ORDER BY部分中CASE语句的“X LAST”。

看起来似乎不行。

我正在尝试按两个列（很容易）对表进行排序，但按特定顺序获取输出（很容易），并且使一列的一个特定值出现在最后（完成了...但很丑）。

假设这些列是zone和status（别怪我给列命名为zone - 我没有命名）。 status只有2个值（'U'和'S'），而zone可以取大约100个值中的任意一个。 zone值的一个子集是（伪正则表达式）IN[0-7]Z，它们在结果中首先出现。使用CASE很容易实现。 zone还可以取值“Future”，该值应出现在结果的最后。

以我通常的混乱方式，我只是强制将CASE值设置为1000，如下所示：

group by zone, status
order by (
 case when zone='IN1Z' then 1
      when zone='IN2Z' then 2
      when zone='IN3Z' then 3
        .
        . -- other IN[X]Z etc
        .
      when zone = 'Future' then 1000
      else 11 -- [number of defined cases +1]
      end), zone, status

这样做虽然可行，但显然是一种折衷方法，我想知道是否有一行代码可以实现相同的效果。
有没有更简洁的方法来达到同样的结果？

- GT.

---

通常的内容缺失：Postgres 版本和表定义（在 psql 中使用 \d tbl 可以获取），这将显示我们精确的数据类型和 NOT NULL 约束。 - Erwin Brandstetter

Answer 1

Postgres允许在ORDER BY子句中使用boolean值，因此这是您的通用“X LAST”：

ORDER BY (my_column = 'X')

该表达式求值为布尔值，结果值按以下方式排序：

FALSE (0)
TRUE (1)
NULL

既然我们处理的是非空值，那么我们只需要这个。以下是您需要的一行代码：

...
ORDER BY (zone = 'Future'), zone, status;

相关：

• 如何将null值排在所有其他值之后，除了特殊值
• 查询结果从第三条记录开始显示的选择查询语句
• 从一个group-by中获取两个条件的SQL查询

- Erwin Brandstetter

Answer 2

我不熟悉具体的postgreSQL，但我曾经在MS SQL server中处理过类似的问题。据我所知，解决这种问题的唯一“好”方法是创建一个单独的区域值表，并为每个区域分配一个排序序列。

例如，我们称该表为ZoneSequence：

Zone   | Sequence
------ | --------
IN1Z   |        1
IN2Z   |        2
IN3Z   |        3
Future |     1000

等等。然后您只需将ZoneSequence加入到查询中，并按Sequence列排序（确保添加良好的索引！）。

这种方法的好处是，当创建新的区域代码时，它很容易维护，因为它们可能会被创建。

- beercohol