每个类别的MySQL限制结果

一尘不染

每个类别的MySQL限制结果

sql

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

   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”。

阅读 102

2021-05-05

共1个答案

一尘不染

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

使用：

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

2021-05-05