执行查询时出现LIMIT[0.25]错误

Question

这就是我试图编写查询的内容

select posts.*  ,users.* 
FROM posts
INNER JOIN users
ON posts.user_id = users.id
WHERE posts.user_id != '27'
AND posts.pid EXISTS(SELECT post_id FROM favourites WHERE posts.pid=favourites.post_id)

这是错误

select posts.*  ,users.* 
FROM posts
INNER JOIN users
ON posts.user_id = users.id
WHERE posts.user_id != '27'
AND posts.pid EXISTS(SELECT post_id FROM favourites WHERE posts.pid=favourites.post_id) LIMIT 0, 25
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS(SELECT post_id FROM favourites WHERE posts.pid=favourites.post_id) LIM...' at line 6

Answer 1

您必须使用IN而不是ÉXISTS。

存在的语法是不同的

SELECT 
posts.*, users.*
FROM
posts
INNER JOIN
users ON posts.user_id = users.id
WHERE
posts.user_id != '27'
AND posts.pid IN (SELECT 
post_id
FROM
favourites
WHERE
posts.pid = favourites.post_id)
LIMIT 0 , 25

这将检查是否有发布id 的收藏夹

SELECT 
posts.*, users.*
FROM
posts
INNER JOIN
users ON posts.user_id = users.id
WHERE
posts.user_id != '27'
AND EXISTS (SELECT 
post_id
FROM
favourites
WHERE
posts.pid = favourites.post_id)
LIMIT 0 , 25