如何在mysql查询中按行限制分组? [英] How do you limit the group by rows in an mysql query?

问题描述

这里还有其他问题，听起来很相似，但并非如此.我有一个查询，返回一堆带有group by的行，我想对行的总数进行限制，而不是对用于创建组的行总数进行限制.

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.

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已用尽我的极限

^^^^ 2 GROUP BY ROWS THAT HAVE ALREADY EXHAUSTED MY LIMIT

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.

谢谢.

推荐答案

这对我有用:

SELECT type, size, COUNT(*) AS rows
FROM clothes
WHERE size = 'large'
GROUP BY type
LIMIT 0, 5

结果:

type      size   rows
------------------------
Circle    Large     4
Oval      Large     1
Square    Large     1
Star      Large     1
Triangle  Large     2

LIMIT应该在GROUP BY之后申请，所以我不明白这个问题.

LIMIT should get applied after GROUP BY, so I don't understand the issue.

这篇关于如何在mysql查询中按行限制分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！