mysql - 连接两个按共同值(热度)排序的不同表

Question

我正在尝试从两个不同的不相关表(showcase 和 questions)中选择结果以显示在 Feed 中。它们应该按公共(public)列 hotness 排序，该列是一个浮点值。

SELECT s.id,s.date,s.title,s.views,s.image,s.hidpi,s.width,s.description,u.display_name,u.avatar
FROM showcase AS s
INNER JOIN users AS u ON s.user_id = u.id
UNION 
SELECT q.id,q.date,q.title,q.views,q.text,u.display_name,u.avatar,0,0,0
FROM questions AS q
INNER JOIN users AS u ON q.user_id = u.id
ORDER BY hotness DESC
LIMIT 10

我已经尝试过UNION，但我不知道应该如何在这里使用它并收到此错误未知列热度

Answer 1

您需要选择该值，ORDER BY 才能识别它:

SELECT s.id,s.date,s.title,s.views,s.image,s.hidpi,s.width,s.description,u.display_name,u.avatar, s.hotness
FROM showcase AS s
INNER JOIN users AS u ON s.user_id = u.id
UNION ALL
SELECT q.id,q.date,q.title,q.views,q.text,u.display_name,u.avatar,0,0,0, q.hotness
FROM questions AS q
INNER JOIN users AS u ON q.user_id = u.id
ORDER BY hotness DESC;

请注意，我还将 UNION 更改为 UNION ALL。除非您打算删除重复项，否则没有理由为此进行额外的处理。