MySQL:如何获得x每个分组的结果数 [英] MySQL: how to get x number of results per grouping

问题描述

可能重复:
mysql:在GROUP BY中使用LIMIT每组获得N个结果?

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

我有两个表:

1. 物品
2. 类别

每个项目都属于一个类别.我想做的是每个类别选择5个项目，但总共说20个项目.

Each item belongs to a category. What I want to do is select 5 items per category but say 20 items in total.

SELECT 

   item_id, item_name, items.catid 

FROM

   items, categories

WHERE

   items.catid = categories.catid

GROUP BY items.catid LIMIT 0,5 //5 per category group

如果每个类别有5个以上的项目，则应按item_id(数字值)对其进行排序

if there are more than 5 items per category - they should be ordered by the item_id (numeric value)

推荐答案

尝试此查询-

SELECT item_id, item_name, catid FROM 
  (SELECT t1.*, COUNT(*) cnt FROM items t1
    LEFT JOIN items t2
      ON t2.catid = t1.catid AND t2.item_id <= t1.item_id 
  GROUP BY
    t1.catid, t1.item_id
  ) t
WHERE
  cnt < 6
-- LIMIT 20

它将显示每个类别的前5个项目.如果需要，请取消注释LIMIT 20.如果需要，请加入Categories表.

It will show first 5 items per category. Uncomment LIMIT 20 if you need. Join Categories table if you need.

这篇关于MySQL:如何获得x每个分组的结果数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！