mysql - 连接两个选择查询并对结果进行排序

Question

基本上我只是不确定为什么这个查询无法执行:

(SELECT replies.reply_post, replies.reply_content, replies.reply_date AS d, members.username
     FROM (replies) AS a 
        INNER JOIN members ON replies.reply_by = members.id)

            UNION

(SELECT posts.post_id, posts.post_title, posts.post_date AS d, members.username 
     FROM (posts) as b 
        WHERE posts.post_set = 0 
           INNER JOIN members ON posts.post_by = members.id)

ORDER BY d DESC LIMIT 5

我收到此错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a INNER JOIN members ON replies.re' at line 2

我想做的就是从这两个表中选择最近的 5 行(日期)。我尝试过 Join、union 等，并且看到许多查询，人们在 FROM 语句之后放置了另一个查询，这对我来说完全没有逻辑意义，不知道它是如何工作的？

我可以肯定地说您可以从两个不同但已连接的查询连接同一个表吗？或者我采取了完全错误的方法，因为坦率地说，尽管阅读了错误消息，但我似乎看不到这个查询是如何失败的。

(两个查询本身工作正常)

Answer 1

我认为您的查询中以下部分存在语法错误:

FROM (posts) as b 
        WHERE posts.post_set = 0 
           INNER JOIN members ON posts.post_by = members.id)

内连接应先于 where 条件。另外你的加入条件也是错误的。您需要应用诸如

之类的条件

INNER JOIN members ON a.reply_by = members.id)
INNER JOIN members ON b.post_by = members.id)

所以你的查询应该是这样的

(SELECT a.reply_post, a.reply_content, a.reply_date AS d, members.username
 FROM (replies) AS a 
 INNER JOIN members ON a.reply_by = members.id)

 UNION

(SELECT b.post_id, b.post_title, b.post_date AS d, members.username 
 FROM (posts) as b 
 INNER JOIN members ON b.post_by = members.id
 WHERE b.post_set = 0)

ORDER BY d DESC LIMIT 5