问题:
Consider the following query:
SELECT u.id
, u.name
, p.id
FROM users u
LEFT
JOIN posts p
ON p.id IN(
SELECT x.id
FROM posts x
WHERE x.user_id =...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
Consider the following query:
SELECT u.id
, u.name
, p.id
FROM users u
LEFT
JOIN posts p
ON p.id IN(
SELECT x.id
FROM posts x
WHERE x.user_id = u.id
ORDER
BY x.featured DESC
LIMIT 10
);
I am trying to join the posts table to the users table. However I only want to retrieve a maximum of 10 posts per user.
This approach throws the following error:
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Is there an alternative approach to achieve the desired result?
回答1:
You should use an inner join on subquery instead of left join and IN clause
SELECT users.id, users.name, t.id
FROM users
INNER JOIN (
SELECT posts.id, posts.user_id
FROM posts
WHERE posts.user_id = users.id
ORDER BY posts.featured DESC
LIMIT 10 ) t on t.user_id = users.id
In this way you don't use a limit inside a In clause for subquery and this should not raise the error .. the error for both LIMIT & IN ....
but not for athe sue of in limit in subquery in JOIN (withou IN clause)