SELECT t1.COMMENT_ID,
       t1.CONTENT,
       t1.CREATE_DATE
FROM COMMENTS t1
LEFT JOIN REPLY_COMMENTS t2
    ON t1.COMMENT_ID = t2.COMMENT_ID
ORDER BY COALESCE(t2.REPLY_TO_COMMENT_ID, t1.COMMENT_ID),
         t1.CREATE_DATE

说明:

ORDER BY 子句使用两个术语进行排序.第一个 COALESCE 术语将返回父消息的 COMMENT_ID(对于父消息和单个后代).这样做的原因是，对于孩子，它将使用加入的 ID，而对于父母，如果发现 NULL，它也将默认为父 ID.第二个排序项使用创建日期，假设对帖子的所有回复都将发生在原始帖子之后.

The ORDER BY clause uses two terms for ordering. The first COALESCE term will return the COMMENT_ID of the parent message (for both parents and single descendant children). The reason this works is that for children it will used the joined ID, and for parents, finding NULL it will also default to the parent ID. The second ordering term uses the creation date, under the assumption that all replies to a post will occur after the original post.