每个类别的MySQL限制结果 [英] MySQL limit results per category

问题描述

c_data中的每个项目都在一个类别/部分中.我想限制每个类别显示多少个项目，而不是限制检索到的项目总数.显然，如果在查询中添加"limit 20"之类的内容，则总共只能获取20个结果，而不是每个类别20个结果.

Each item in c_data is in a category/section. I would like to limit how many items are displayed per category, rather than limiting the total number of items retrieved. Obviously if I add something like "limit 20" to the query, it will only fetch 20 results in total, rather than 20 results per category.

   SELECT cm.id,
                cm.title AS cmtitle,
                cm.sectionid,
                cm.type AS cmtype,
                cd.id,
                cd.time,
                cd.link,
                cd.title,
                cd.description,
                cd.sectionid AS sectionid
      FROM c_main AS cm
      JOIN c_data AS cd ON cd.sectionid=cm.sectionid
     WHERE cd.sectionid=cm.sectionid 
     ORDER by id ASC

类别为"sectionid"的字段.

The field with the category is "sectionid".

推荐答案

MySQL没有任何排名功能，但是您可以使用变量来创建伪行号.

MySQL doesn't have any ranking functionality, but you can use a variable to create a psuedo row number.

使用:

SELECT x.*
  FROM (SELECT cm.id,
               cm.title AS cmtitle,
               cm.sectionid,
               cm.type AS cmtype,
               cd.id AS cd_id,
               cd.time,
               cd.link,
               cd.title,
               cd.description,
               cd.sectionid AS cd_sectionid,
               CASE
                 WHEN @sectionid != cm.sectionid THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1
               END AS rank,
               @sectionid := cm.sectionid
          FROM C_MAIN cm,
               C_DATA cd,
               (SELECT @rownum := 0, @sectionid := NULL) r
         WHERE cm.sectionid = cd.sectionid
      ORDER BY cm.sectionid) x
 WHERE x.rank <= 20
ORDER BY id

这篇关于每个类别的MySQL限制结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！