如何在MySQL中实现GROUP BY范围？
为了在MySQL中按范围分组，让我们首先创建一个表。创建表的查询如下
mysql> create table GroupByRangeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > YourRangeValue int - > );
现在，您可以使用insert命令在表中插入一些记录。
查询如下
mysql> insert into GroupByRangeDemo(YourRangeValue) values(1); mysql> insert into GroupByRangeDemo(YourRangeValue) values(7); mysql> insert into GroupByRangeDemo(YourRangeValue) values(9); mysql> insert into GroupByRangeDemo(YourRangeValue) values(23); mysql> insert into GroupByRangeDemo(YourRangeValue) values(33); mysql> insert into GroupByRangeDemo(YourRangeValue) values(35); mysql> insert into GroupByRangeDemo(YourRangeValue) values(1017);
使用select语句显示表中的所有记录。
查询如下
mysql> select *from GroupByRangeDemo;
以下是输出
+----+----------------+ | Id | YourRangeValue | +----+----------------+ | 1 | 1 | | 2 | 7 | | 3 | 9 | | 4 | 23 | | 5 | 33 | | 6 | 35 | | 7 | 1017 | +----+----------------+ 7 rows in set (0.04 sec)
这是按范围分组的查询
mysql> select round(YourRangeValue / 10), count(YourRangeValue) from GroupByRangeDemo where YourRangeValue < 40 group by round(YourRangeValue / 10) - > union - > select '40+', count(YourRangeValue) from GroupByRangeDemo where YourRangeValue >= 40;
以下是输出
+----------------------------+-----------------------+ | round(YourRangeValue / 10) | count(YourRangeValue) | +----------------------------+-----------------------+ | 0 | 1 | | 1 | 2 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 40+ | 1 | +----------------------------+-----------------------+ 6 rows in set (0.08 sec)