MySql - apply limit to joined rows only

问题: 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)

  • 发表于 2019-02-15 03:45
  • 阅读 ( 187 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除