mysql - 如何在工会中订购2张 table ？

我正在尝试获取消息列表，一个表是所有未读消息的列表，另一个是所有已读消息的列表。我希望未读邮件在上次更新时排序，以便最新邮件始终位于顶部。

我写了以下查询，除了我得到一个错误，即订单依据未与联合啮合。我尝试将两个语句都用括号括起来，但是顺序错误。

SELECT threads.id AS thread,
       updated_at
FROM   threads
WHERE  threads.id IN (SELECT thread_id
                      FROM   posts
                      WHERE  posts.id IN (SELECT post_id
                                          FROM   post_recipients
                                          WHERE  status = 1
                                             AND
                             user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'))
ORDER  BY updated_at DESC
UNION
SELECT thread_id AS thread,
       updated_at
FROM   thread_assignments
WHERE  user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'
   AND status = 0
   AND thread_id NOT IN (SELECT threads.id AS thread
                         FROM   threads
                         WHERE  threads.id IN (SELECT thread_id
                                               FROM   posts
                                               WHERE
                                posts.id IN (SELECT post_id
                                             FROM   post_recipients
                                             WHERE  status = 1
                                                AND
       user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'))
                        )
ORDER  BY updated_at DESC;

需要明确的是，我不希望由updated_at排序的最终表，因为这可能会使已读消息比未读消息高。我要单独订购2张桌子，然后粘在一起。

最佳答案

根据Teson的建议，创建一个附加字段，以便您可以将两种类型分开

SELECT threads.id AS thread,
       updated_at ,
       0 as union_source
FROM   threads
WHERE  threads.id IN (SELECT thread_id
                      FROM   posts
                      WHERE  posts.id IN (SELECT post_id
                                          FROM   post_recipients
                                          WHERE  status = 1
                                             AND
                             user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'))
UNION
SELECT thread_id AS thread,
       updated_at,
       1 as union_source
FROM   thread_assignments
WHERE  user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'
   AND status = 0
   AND thread_id NOT IN (SELECT threads.id AS thread
                         FROM   threads
                         WHERE  threads.id IN (SELECT thread_id
                                               FROM   posts
                                               WHERE
                                posts.id IN (SELECT post_id
                                             FROM   post_recipients
                                             WHERE  status = 1
                                                AND
       user_id = 'b1367cad-e1ea-31ba-beb9-2b53559eea17'))
                        )
ORDER  BY
      union_source,
      updated_at DESC;

关于mysql - 如何在工会中订购2张 table ？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53619129/