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

本文介绍了在所有其他值之后对空值进行排序，特殊值除外的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有可选排序字段的 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