MySQL中Many 2 Many的Order by子句 [英] Order by clause for Many 2 Many in MySQL

问题描述

我有这样的表格/记录:

I have tables/records like this:

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 to Content 2 | 2016-09-04
      5    | Reply to Content 1 | 2016-09-05
      6    | Reply to Content 2 | 2016-09-03


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

我想按照这个顺序显示记录:

And I'd like to show the records like this order:

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

所以回复"内容应该在父内容下 - 但回复内容也应该按 CREATE_DATE 排序.

So the 'reply' contents should be under the parent's content - but the reply contents also should be ordered by CREATE_DATE.

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

Basically, I want to put together: content and reply with order of 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).

I can't write the order by clause with my current knowledge - please help me out (I'm using MySQL).

我只想使用 COMMENTS.CREATE_DATE 字段 - 不想使用 COMMENT_ID 字段，因为它是主键(是吗?甚至可能?).

I only want to use the COMMENTS.CREATE_DATE field - don't want to use COMMENT_ID field since it's primary key (is it even possible?).

推荐答案

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.

这篇关于MySQL中Many 2 Many的Order by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！