MySQL:如何获得x每个分组的结果数 [英] MySQL: how to get x number of results per grouping
本文介绍了MySQL:如何获得x每个分组的结果数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?
我有两个表:
- 物品
- 类别
每个项目都属于一个类别.我想做的是每个类别选择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屋!
查看全文