mysql - Many 2 Many 中的递归方式排序

Question

我有这样的表/记录:

Table: COMMENTS
---------------------------------------------
COMMENT_ID | CONTENT            | CREATE_DATE
---------------------------------------------
      1    | Content 1          | 2016-09-01
      2    | Content 2          | 2016-09-02
      3    | Content 3          | 2016-09-03
      4    | Reply 2-1          | 2016-09-04
      5    | Reply 1-1          | 2016-09-05
      6    | Reply 1-1-1        | 2016-09-06


Table: REPLY_COMMENTS
---------------------------------
COMMENT_ID | REPLY_TO_COMMENT_ID
---------------------------------
     4     |         2
     5     |         1
     6     |         5

我想按如下顺序显示记录:

---------------------------------------------
COMMENT_ID | CONTENT            | CREATE_DATE
---------------------------------------------
      1    | Content 1          | 2016-09-01
      5    | Reply 1-1          | 2016-09-05
      6    | Reply 1-1-1        | 2016-09-06
      2    | Content 2          | 2016-09-02
      4    | Reply 2-1          | 2016-09-04
      3    | Content 3          | 2016-09-03

因此“回复”内容应位于父级内容下 - 但回复内容也应按 CREATE_DATE 排序。

基本上，我想将内容和回复按 CREATE_DATE 的顺序放在一起。

我这样写了查询:

SELECT  comment.*
FROM COMMENTS comment
LEFT JOIN REPLY_COMMENTS reply_comment ON reply_comment.COMMENT_ID = comment.COMMENT_ID

ORDER BY (SOMETHING SHOULD BE HERE), comment.CREATE_DATE ASC

以我目前的知识，我无法编写 order by 子句 - 请帮助我(我正在使用 MySQL)。

Answer 1

如果字符和数值之间始终有空格，那么它可以正常工作(对于当前数据)，否则您可以尝试以棘手的方式使用字符串函数:

COMMENT_ID  CONTENT        CREATE_DATE
----------  -------------  -----------
     1  Content 1      2016-09-01 
     2  Content 2      2016-09-02 
     3  Content 3      2016-09-03 
     4  Reply 2-1      2016-09-04 
     5  Reply 1-1      2016-09-05 
     6  Reply 1-1-1    2016-09-06 
     7  Reply 1-10-12  2016-09-06

SELECT  comment.*
FROM COMMENTS COMMENT
LEFT JOIN REPLY_COMMENTS reply_comment ON reply_comment.COMMENT_ID = comment.COMMENT_ID
ORDER BY REPLACE(content, LEFT(content, LOCATE(' ', content) - 1), '')

--OUTPUT--

COMMENT_ID  CONTENT        CREATE_DATE
----------  -------------  -----------
     1  Content 1      2016-09-01 
     5  Reply 1-1      2016-09-05 
     6  Reply 1-1-1    2016-09-06 
     7  Reply 1-10-12  2016-09-06 
     2  Content 2      2016-09-02 
     4  Reply 2-1      2016-09-04 
     3  Content 3      2016-09-03