sql - 在所有其他值之后对空值进行排序，特殊情况除外

Question

我有一个带有可选排序字段的 PostgreSQL 项目表:

CREATE TABLE tasks (
  id     integer  PRIMARY KEY DEFAULT nextval('f_seq'),
  f_id   integer  REFERENCES fixins,
  name   text     NOT NULL,
  sort   integer
);

我希望没有 sort 值的任务排在所有其他任务之后，但有一个异常(exception):如果 sort = -1 我希望它排在其他任务之后。因此，例如，给定这些值:

id | f_id |   name   | sort
---+------+----------+-------
 1 |    1 | zeta     |    -1
 2 |    1 | alpha    |     1
 3 |    1 | gamma    |     3
 4 |    1 | beta     |     2
 5 |    1 | delta    |     
 6 |    1 | epsilon  |     

我希望它们排序为:alpha、beta、gamma、delta、epsilon ，zeta。

我知道我可以使用 ORDER BY COALESCE(sort,99999) 在非空值之后对空值进行排序，但是我怎样才能得到那个特殊的 -1 值去追那些？

Answer 1

更简单:

SELECT *
FROM   tasks
ORDER  BY (sort IS NOT DISTINCT FROM -1), sort;

如何？

Postgres 有一个合适的 boolean 类型(不像其他一些 RDBMS)。您可以像按任何其他数据类型一样按它排序。并且它可以像任何其他数据类型一样为 NULL。默认排序顺序为:

FALSE (0)
TRUE (1)
NULL

(sort IS NOT DISTINCT FROM -1) 对除 -1 之外的所有值求值为 FALSE - 它求值为 TRUE 最后排序。只需将 sort 添加为辅助 ORDER BY 项即可。

等价选择:

SELECT *
FROM   tasks
ORDER  BY (sort IS DISTINCT FROM -1) DESC, sort;

db<> fiddle here
<子>旧sqlfiddle