你如何在 mysql 查询中按行限制分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11058911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you limit the group by rows in an mysql query?
提问by Tomas
There are other questions on here that sound similar but are not. I have a query that returns a bunch of rows with group by and I want to apply a limit to the total group by rows, not the total rows used to create the groups.
这里还有其他问题听起来很相似，但实际上并非如此。我有一个查询，它返回一堆带有 group by 的行，我想对 group by 总行数应用限制，而不是用于创建组的总行数。
ID TYPE COLOR SIZE
----------------------------------------
1 Circle Blue Large
2 Circle Red Large
3 Square Green Large
4 Circle Purple Large
5 Circle Blue Small
6 Circle Yellow Medium
7 Circle Black Large
8 Oval Blue Large
9 Circle Gray Small
10 Triangle Black Large
11 Star Green Large
12 Triangle Purple Large
SELECT size, type FROM clothes WHERE size = 'large' GROUP BY type LIMIT 0, 5
TYPE SIZE ROWS
---------------------------
Circle Large 4
Square Large 1
^^^^ 2 GROUP BY ROWS THAT HAVE ALREADY EXHAUSTED MY LIMIT
^^^^ 2 行已用尽我的极限
TYPE SIZE ROWS
---------------------------
Circle Large 4
Square Large 1
Oval Large 1
Triangle Large 2
Star Large 1
^^^^ HERE'S WHAT I WANT, LIMIT APPLIED TO THE GROUPS
^^^^ 这就是我想要的，适用于组的限制
There must be some subquery or something I can do here, but I'm not figuring it out.
一定有一些子查询或我可以在这里做的事情，但我没有弄清楚。
Thanks.
谢谢。
采纳答案by Ami
This works for me:
这对我有用：
SELECT type, size, COUNT(*) AS rows
FROM clothes
WHERE size = 'large'
GROUP BY type
LIMIT 0, 5
Results in:
结果是：
type size rows
------------------------
Circle Large 4
Oval Large 1
Square Large 1
Star Large 1
Triangle Large 2
LIMIT should get applied after GROUP BY, so I don't understand the issue.
LIMIT 应该在 GROUP BY 之后应用，所以我不明白这个问题。
回答by Eugen Rieck
SELECT * FROM (
SELECT id, color, size, type FROM clothes WHERE size = 'large' GROUP BY type
) AS baseview LIMIT 0, 25
回答by Matt Westlake
how about just counting how many there are?
数一数有多少呢？
select color, size, type, count(id) from clothes where size = 'large' group by size, type, color;
I'm new to sql programming but this should return the following, that way you use 1 line for each color/size/type combination and get a quantity count after it.
我是 sql 编程的新手，但这应该返回以下内容，这样您就可以为每个颜色/尺寸/类型组合使用 1 行并在其后获得数量计数。
Red large shirt 4
红色大衬衫4
Green large shirt 6
绿色大衬衫6
Green large pants 2
绿色大裤衩2
and so on...
等等...
