我有两个查询选择相似的列,但有不同的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
我该怎么做?
最佳答案
您应该能够在两个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
关于mysql - 结合两个不同的查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22209069/