将空值排在所有其他值之后，除非特殊 [英] 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

对于除-1以外的所有值，

(sort IS NOT DISTINCT FROM -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;

SQL小提琴.

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