极客教程MySQL 如何实现GROUP BY范围
要在MySQL中按范围分组，请先创建一个表。创建表的查询如下：
mysql> create table GroupByRangeDemo
- > (
- > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- > YourRangeValue int
- > );
Query OK, 0 rows affected (0.78 sec)
现在可以使用insert命令将一些记录插入表中。
查询如下：
mysql> insert into GroupByRangeDemo(YourRangeValue) values(1);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(7);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(9);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(23);
Query OK, 1 row affected (0.13 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(33);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(35);
Query OK, 1 row affected (0.16 sec)
mysql> insert into GroupByRangeDemo(YourRangeValue) values(1017);
Query OK, 1 row affected (0.11 sec)
使用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)
阅读更多：MySQL 教程