mysql组并排序UNION

| 在我的留言簿中，我有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（id5ѭ）结果应该是什么样的>

- 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 个回复

脾萌

对于这个答案，我将假设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也具有较ѭ9的时间戳，则此方法有效。原始代码备注在原始代码中

GROUP BY id 
ORDER BY date_added DESC, is_reply ASC

GROUP BY在idASC上隐式排序；接下来的ORDER BY优先于that9 and，第二orders15ѭ。但是，如果date_added是datetime，那么两个帖子具有相同时间的机会就很少（尤其是对于答复，写它们要花一些时间），因此，几乎不使用二阶子句。仅当您在select中具有聚合函数时才使用GROUP BY，例如SUM或COUNT 如果要从选择项中删除重复项，请不要使用分组依据，请使用distinct22中的distinct

摊揉售

类似的解决方案：

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

号拳藐孔妇

我建议在两个字段中都添加一个消息父字段，并将其作为您的主要排序，然后将日期排序为之后的排序..否则，您将看到显示的答复与在答复之间发布的其他消息混合在一起。您可以将不答复消息的消息父项本身设置为。

藕挝

尝试这个：

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

要回复问题请先登录或注册

mysql组并排序UNION

4 个回复

发起人

mysql

问题状态

mysql组并排序UNION

与内容相关的链接

4 个回复

发起人

mysql

问题状态