mysql组并对UNION进行排序

In my guestbook I have 2 tables: messages and replies. Now, I want to get all messages grouped by id (means that message and corresponding replies will be grouped/together) and sorted by date DESC (the newest messages will be first; if a message is the oldest one, but the corresponding reply is the newest of all messages, this group will be on the top of the table), while replies will be sorted by date ASC (oldest reply on the top). Here my mysql query that works good except it doesnt sort replies by date ASC

在我的留言簿中，我有2个表：消息和回复。现在，我希望获取按ID分组的所有消息（意味着消息和相应的回复将被分组/一起）并按日期DESC排序（最新的消息将是第一个;如果消息是最早的消息，但相应的回复是所有消息中的最新消息，该组将位于表的顶部），而回复将按日期ASC排序（最顶层的回复）。这里我的mysql查询运行良好，除了它没有按日期ASC排序回复

SELECT msg.id as id, msg.comment, msg.date_added as date_added, 0 as is_reply
  FROM messages AS msg 
UNION 
SELECT reply.msg_id as id, reply.comment, reply.date_added as date_added, 1 as is_reply
  FROM pg_reply as reply 

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC 

is_reply ASC doesnt do the job as I supposed

is_reply ASC不像我想的那样完成这项工作

reply.msg_id specifies id of reply's parent (messages.id)

reply.msg_id指定回复父的id（messages.id）

What the result should look like>

结果应该是什么样的>

- message A
- oldest reply B 
- old reply C
- new reply Z  // this is the newest message in the guestbook 
- newer message E // is newer than A but older than the newest message in the guestbook, which is Z
- reply F // (this reply is newer than all messages but message Z)

4 个解决方案

#1

For this answer I'm going to assume that reply.msg_id is a linkfield to the original message.

对于这个答案，我将假设reply.msg_id是原始消息的链接字段。

SELECT id, comment, date_added, is_reply FROM (
  SELECT 
    msg.id as id
    , msg.comment
    , msg.date_added as date_added
    , 0 as is_reply
    FROM messages AS msg 
UNION 
  SELECT 
    reply.msg_id as id
    , reply.comment
    , reply.date_added as date_added
    , 1 as is_reply
  FROM pg_reply as reply ) AS allmsg
ORDER BY id DESC, is_reply, date_added DESC

This works assuming that msg_id is an autoincrement field and that newer id's also have a newer date_added timestamp.

这可以假设msg_id是一个自动增量字段，而较新的id也有一个较新的date_added时间戳。

Remarks on the original code
In your original code you have

关于原始代码的备注在您的原始代码中

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC

The GROUP BY implicitly orders on id ASC; the following ORDER BY overrides that and orders by date_added first and is_reply second.
However if date_added is a datetime then the chance of two post having the same time are remote (esp. for replies, it takes time to write them),
so the 2nd order clause hardly ever gets used.

GROUP BY隐含地在id ASC上订购;以下ORDER BY首先覆盖date_added和is_reply秒的命令和命令。但是如果date_added是一个日期时间，则两个帖子具有相同时间的可能性是远程的（特别是对于回复，它需要时间来编写它们），因此第二个订单子句几乎不会被使用。

GROUP BY should only be used if you have an aggregate function in your select, such as SUM or COUNT

只有在select中有聚合函数时才应使用GROUP BY，例如SUM或COUNT

If you want to remove duplicates from your select do not use group by, use distinct as in select distinct a,b,c from table1 where ...

如果你想从你的选择中删除重复项，请不要使用group by，使用distinct作为select table a，b，c from table1 where ...

#2

similar solution:

类似方案：

SELECT sort, project, reviewdate, reviewby, subject, venue, filename, remarks1, remarks2, url
FROM (

   SELECT '1' AS sort, project, last_updated AS reviewdate, reviewby, concat( project, '(', 
    TYPE , '): ', subject ) AS subject, venue, filename, remarks1, remarks2, concat( project, '/', 
    TYPE , '/', filename ) AS url
   FROM `upload_cmg` 
   WHERE (
     (
      subject LIKE '%prt%'
      OR project LIKE '%prt%'
      OR TYPE LIKE '%prt%'
     )
     AND (
      subject LIKE '%mouda%'
      OR project LIKE '%mouda%'
      OR TYPE LIKE '%mouda%'
     )
   )
   UNION SELECT '2' AS sort, project, last_updated AS reviewdate, reviewby, concat( project, '(', 
    TYPE , '): ', subject ) AS subject, venue, filename, remarks1, remarks2, concat( project, '/', 
    TYPE , '/', filename ) AS url
   FROM `upload_cmg` 
   WHERE subject LIKE '%mouda%'
   GROUP BY url
) AS vin
ORDER BY sort, reviewdate DESC 

#3

I would recommend adding a message parent field in both and sorting on that as your primary sort, with date then being the sort after that.. otherwise you will have replies showing up mixed up with other messages that were posted between replies. You can have the message parent of a non-reply message be itself.

我建议在两者中添加一个消息父字段，并将其作为主要排序进行排序，然后将日期作为排序后的排序。否则，您将看到与回复之间发布的其他消息混淆的回复。您可以将非回复消息的父消息本身。

#4

Try this:

尝试这个：

SELECT id, comment, date_added, is_reply
FROM (
    SELECT msg.id as id, msg.comment, msg.date_added as date_added, 0 as is_reply
      FROM messages AS msg 
    INNER JOIN pg_reply as reply
    ON reply.msg_id = msg.id
    GROUP BY msg.id, msg.comment, msg.date_added, 0 as is_reply
    ORDER BY CASE WHEN MAX(reply.date_added) > msg.date_added THEN MAX(reply.date_added) ELSE msg.date_added END DESC
    UNION 
    SELECT reply.msg_id as id, reply.comment, reply.date_added as date_added, 1 as is_reply
    FROM pg_reply as reply 
    ORDER BY date_added ASC ) a
ORDER BY id