PostgreSQL中ORDER BY双时间戳列排序问题求助

要实现按「创建或修改时间的最近值」排序，你需要取每个帖子的created和modified中的较大值作为排序依据，同时处理modified为NULL的情况（未修改过的帖子）。

修改你的SQL语句，将ORDER BY子句替换为：

SELECT posts.postid, users.id, posts.modified, posts.created
FROM posts
JOIN users ON posts.userid=users.id
WHERE posts.isdraft=false
ORDER BY GREATEST(posts.created, COALESCE(posts.modified, posts.created)) DESC;

逻辑解释 #

• COALESCE(posts.modified, posts.created)：当modified为NULL时，用created值替代，确保未修改的帖子能以创建时间参与比较。
• GREATEST(a, b)：取两个时间中的较大值，保证无论是新创建的帖子，还是最近修改的旧帖子，都能按最新的时间排序。

如果你的业务逻辑能保证modified永远不会早于created（正常场景下修改时间必然晚于创建时间），可以简化为更高效的写法：

ORDER BY COALESCE(posts.modified, posts.created) DESC;

优化建议 #

如果帖子数量较多，排序性能可能受影响，建议创建表达式索引来加速排序：

-- 对应完整写法的索引
CREATE INDEX idx_posts_latest_activity ON posts (GREATEST(created, COALESCE(modified, created)) DESC);

-- 对应简化写法的索引
CREATE INDEX idx_posts_latest_activity ON posts (COALESCE(modified, created) DESC);

修改后的预期结果 #

执行修改后的SQL后，post135会排在首位，符合你的需求：

postid | id  |           modified            |            created
--------+-----+-------------------------------+-------------------------------
    135 | 999 |                               | 2022-11-28 02:28:20.64009-06
    100 | 999 | 2022-11-28 01:57:07.495482-06 | 2022-11-27 21:43:34.132985-06
    115 | 111 | 2022-11-28 01:55:05.9358-06   | 2022-11-27 21:43:34.137873-06
    130 | 444 |                               | 2022-11-28 01:42:49.301489-06
    110 |  42 |                               | 2022-11-27 21:43:34.137254-06

内容的提问来源于stack exchange，提问作者Ryan Knutson