mysql - 组合两个不同的查询

Question

我有两个查询选择相似的列，但具有不同的 WHERE 和 JOIN 子句。我需要将这两个查询与 UNION 结合起来，以便总共显示 26 个结果(组合)，按日期排序。我不知道如何使用以下两个查询来做到这一点。

第一个查询:

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p 
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

第二个查询:

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ? AND following_id != ?))
AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

我该怎么做？

Answer 1

您应该能够在两个 select 语句之间放置 UNION 或 UNION ALL。另外，请确保删除第一个 ORDER BY，否则会引发错误。应运行以下语句。

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p 
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
AND p.removed != 1
UNION
SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ? AND following_id != ?))
AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26