如何使用 MySQL 查询中的 UNION 从另一个表中获取记录，如果第一个表没有返回记录

Question

我有两张表，名称分别为 'activities' 和 'archived_activities'。我将 activities 表中的记录分成了另一个表中的记录。Activities 表只包含用户最新的 200 条活动记录，其余记录移至 archived_activities 表中。现在我想在 activities 表返回 null 时连接这两个表，然后使用相同的偏移量和限制从 archived_activities 表中获取下一条记录。以下是我的查询，但它并不能正常工作。

SELECT * FROM activities WHERE user_id=87 LIMIT 180,20
UNION ALL 
SELECT * FROM activities WHERE user_id=87 LIMIT 180,20

但是这个查询并不好用。

有什么帮助吗？

- Rizwan Saleem

Answer 1

一种方法是通过联合操作将当前记录和存档记录组合成一个逻辑表，但要按照优先级排序，以确保当前记录具有更高的优先级。我将当前记录的位置赋为1，存档记录的位置赋为2。然后，按照这个位置排序并保留200条记录。

SELECT col1, col2, ...
FROM
(
    SELECT col1, col2, ..., 1 AS position
    FROM activities
    WHERE user_id = 87
    UNION ALL
    SELECT col1, col2, ..., 2
    FROM archived_activities
    WHERE user_id = 87
) t
ORDER BY
    position
LIMIT 200;

- Tim Biegeleisen

---

似乎比我的解决方案更好 @RizwanSaleem - sagi

Answer 2

你可以使用 NOT EXISTS() ：

SELECT * FROM activities
WHERE user_id=87
LIMIT 180,20
UNION ALL 
SELECT * FROM archieve_activities 
WHERE NOT EXISTS(SELECT 1 FROM activities 
                 WHERE user_id = 87)
  AND user_id=87
LIMIT 180,20

- sagi

---

但是如果第一个表有100条记录，因此需要从存档表中获取100条记录，那么会发生什么？ - Tim Biegeleisen

只有当活动表返回null时，我才会……这听起来似乎不是这种情况。让我们等待OP的评论@TimBiegeleisen - sagi

Answer 3

对于一般情况，UNION的任何答案都不能最优地执行LIMIT 180, 20。

( SELECT ... ORDER BY .. LIMIT 200 )
UNION ALL
( SELECT ... ORDER BY .. LIMIT 200 )
ORDER BY .. LIMIT 180, 20

这将获取“正确”的20行，无论SELECT是否找到少于或多于200行。

（注意，“200”来自“180 + 20”。）

- Rick James

Answer 4

你可以尝试这个查询。

select * from activities as a 
union all
select * from archived_activities as b 
where b.user_id not in 
(select r.user_id
 from activities as r)

- Terrible Coder