如何编写SQL查询让帖子回复紧跟对应主帖排序？

我的帖子表包含post ID和reply_to列，原始数据如下：

1 null
2 null
3 1
4 2
5 1

说明：帖子5是帖子1的回复。我需要构造一条SQL查询，让查询结果满足：回复帖紧跟在它引用的主帖之后，其余情况按ID排序。不存在嵌套回复，主帖的reply_to值始终为null。

期望的查询结果：

1 null
3 1
5 1
2 null
4 2

我尝试用LEAST函数排序，SQL语句如下：

SELECT id, replyto from posts ORDER BY LEAST(id, replyto)

但这个语句会把所有主帖排在最前面，得到的结果不符合需求：

+------+---------+
| id   | replyto |
+------+---------+
|    1 |    NULL |
|    2 |    NULL |
|    3 |       1 |
|    5 |       1 |
|    4 |       2 |
+------+---------+

解决方案 #

可以通过COALESCE函数把回复帖的reply_to值作为排序的第一优先级，同时给主帖和回复帖设置不同的排序标识，让主帖排在对应回复的前面：

SELECT id, reply_to
FROM posts
ORDER BY 
    COALESCE(reply_to, id),  -- 主帖和它的回复用同一个基准值排序
    CASE WHEN reply_to IS NULL THEN 0 ELSE 1 END,  -- 主帖排在回复前面
    id  -- 同组内按ID排序

解释：

1. COALESCE(reply_to, id)：主帖的reply_to是null，所以用自身ID作为排序基准；回复帖用reply_to（即主帖ID）作为基准，这样主帖和它的回复会被分到同一组。
2. CASE WHEN reply_to IS NULL THEN 0 ELSE 1 END：让主帖的排序权重比回复帖小，确保主帖排在同组的最前面。
3. 最后按id排序，保证同组内（主帖的回复）按ID顺序排列。

执行这条SQL后，就能得到你期望的结果。

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