MySQL限制与in语句 [英] Mysql limit with in statement

问题描述

我正在写查询

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本身中做到这一点吗?

it is fetching first 6 bookmarks. But i want it will fetch 6 bookmarks from each category. Can we do this in mysql itself.

请分开...

谢谢

推荐答案

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

You can use a rank query to give a rank for each category and filter it with your desired no of records per category below query will give you 6 records from each category

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

这篇关于MySQL限制与in语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！