如何在PostgreSQL中按距now（）的距离排序（未来优先） [英] How to sort by distance from now() (future first) in PostgreSQL

问题描述

我有一个日期字段，显示为从今天起的天数。因此 2055-01-01 和 1950-01-01 将分别显示为正数和负数。现在，我希望对它们进行排序，以使非负数首先以升序排列，然后负数以降序排列。例如：

I've got a date field which is displayed as the number of days from today. So 2055-01-01 and 1950-01-01 would be displayed as positive and negative numbers respectively. Now I'd like these to be ordered so that the non-negative numbers come first, in ascending order, then negative numbers come in descending order. For example:

0
1
2
3
4
-1
-2
-3

推荐答案

以下内容也可以使用：

 ORDER BY expiry < CURRENT_DATE, abs(expiry - CURRENT_DATE)

但是此表单不会使用索引来生成行以所需的顺序。如果您的查询将从中受益（从表中选择大多数行或使用限制），则需要使用联合：

However this form won't use an index to produce the rows in the desired order. If your query would benefit from that (selects most of the rows from the table or uses a limit), then you'll need to use a union:

SELECT ... WHERE ... AND expiry >= CURRENT_DATE ORDER BY expiry
    UNION ALL
SELECT ... WHERE ... AND expiry < CURRENT_DATE ORDER BY expiry DESC

这篇关于如何在PostgreSQL中按距now（）的距离排序（未来优先）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！