mysql 分组并排序 UNION

Question

在我的留言簿中，我有 2 个表:messages 和 replies。现在，我想将所有消息按 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 没有像我想象的那样完成工作

reply.msg_id 指定回复父级的 id (messages.id)

结果应该是什么样的>

- 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)

Answer 1

对于这个答案，我假设 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

假设 msg_id 是一个自动增量字段并且较新的 id 也有较新的 date_added 时间戳。

原代码注释
在您的原始代码中，您有

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC

GROUP BY 隐式排序 id ASC；以下 ORDER BY overrides 并首先按 date_added 排序，然后按 is_reply 排序。
但是，如果 date_added 是一个 datetime，那么两个帖子具有相同时间的可能性很小(特别是对于回复，写它们需要时间),
所以二阶子句几乎从未被使用过。

GROUP BY 仅当您的 select 中有聚合函数时才应使用，例如 SUM 或 COUNT

如果你想从你的选择中删除重复项不要使用分组依据，使用distinct作为select distinct a,b,c from table1 where ...