Mysql用in语句限制

我正在写一个查询

SELECT 
   `user_bookmarks`.`id` as `user_bookmark_id`,
   `bookmark_id`,
   `user_bookmarks`.`user_id`,
   `bookmark_url`,
   `bookmark_website`,
   `bookmark_title`,
   `bookmark_preview_image`,
   `bookmark_popularity`,`category_id`,
   `category_name`,
   `pdf_txt_flag`,
   `youtube_video`,
   `content_preview`,
   `snapshot_preview_image`,
   `mode` 
FROM 
   `user_bookmarks` 
   left join `bookmarks` 
      on `user_bookmarks`.`bookmark_id` = `bookmarks`.`id` 
   left join `categories` 
      on `user_bookmarks`.`category_id` = `categories`.`id` 
   WHERE 
      `category_id` IN(164,170,172) 
   LIMIT 0, 6

它正在获取前 6 个书签。但我希望它能从每个类别中获取 6 个书签。我们可以在 mysql 本身中做到这一点吗？

请指教..

Thanks

您可以使用排名查询为每个类别提供排名，并使用您所需的每个类别的记录数进行过滤，下面的查询将为您提供每个类别的 6 条记录

SELECT t.* FROM
(SELECT 
  `user_bookmarks`.`id` AS `user_bookmark_id`,
  `bookmark_id`,
  `user_bookmarks`.`user_id`,
  `bookmark_url`,
  `bookmark_website`,
  `bookmark_title`,
  `bookmark_preview_image`,
  `bookmark_popularity`,
  `category_id`,
  `category_name`,
  `pdf_txt_flag`,
  `youtube_video`,
  `content_preview`,
  `snapshot_preview_image`,
  `mode` ,
   @r:= CASE WHEN category_id = @g THEN @r+1  ELSE @r:=1 END `rank` ,
   @g:=category_id
FROM
  `user_bookmarks` 
  LEFT JOIN `bookmarks` 
    ON `user_bookmarks`.`bookmark_id` = `bookmarks`.`id` 
  LEFT JOIN `categories` 
    ON `user_bookmarks`.`category_id` = `categories`.`id` 
  JOIN (SELECT @r:=0,@g:=0) t1
WHERE `category_id` IN (164, 170, 172) 
ORDER BY category_id
) t
WHERE t.rank <=6