在所有其他值之后对空值进行排序，特殊值除外 [英] Sorting null values after all others, except special

问题描述

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

I have a PostgreSQL table of items with an optional ordering field:

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

我希望没有 sort 值的任务在所有其他任务之后排序，只有一个例外:如果 sort = -1 我希望它在这些之后排序.因此，例如，给定这些值:

I want tasks that have no sort value to sort after all others, with one exception: if sort = -1 I want it to sort after those. So, for example, given these values:

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.

I want them to sort as: alpha, beta, gamma, delta, epsilon, zeta.

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

I know that I can use ORDER BY COALESCE(sort,99999) to order null values after non-nulls, but how can I get that special -1 value to come after those?

推荐答案

更简单:

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

怎么样?

Postgres 具有适当的 boolean 类型(与其他一些 RDBMS 不同).您可以像按任何其他数据类型一样按它排序.它可以像任何其他数据类型一样为 NULL.默认排序顺序为:

How?

Postgres has a proper boolean type (unlike some other RDBMS). You can order by it just like by any other data type. And it can be NULL like any other data type. Default sort order is:

FALSE (0)
TRUE (1)
NULL

(sort IS NOT DISTINCT FROM -1) 对除 -1 之外的所有值计算为 FALSE - 计算 TRUE 并排在最后.只需将 sort 添加为辅助 ORDER BY 项.

(sort IS NOT DISTINCT FROM -1) evaluates to FALSE for all values except -1 - which evaluates TRUE and sorts last. Just add sort as secondary ORDER BY item.

等效替代:

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

db<>fiddle 这里
_旧sqlfiddle

这篇关于在所有其他值之后对空值进行排序，特殊值除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！