mysql - 如何在联合体中订购 2 个表？

Question

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

我已经编写了以下查询，但我收到一个错误，即排序依据未与联合啮合。我尝试将这两个语句都括在括号中，但这导致了错误的顺序。

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 排序，因为这可能会使已读消息高于未读消息。我想要两张 table 分开订购然后粘在一起。

Answer 1

按照 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;