mysql> select nation, count(*) from user group by nation;
+----------+----------+
| nation | count(*) |
+----------+----------+
| 汉族 | 2 |
| 回族 | 1 |
| 维吾尔族 | 2 |
| 蒙古族 | 1 |
+----------+----------+
4 rows in set (0.00 sec)
group by可以结合where一起使用,不过where不能在group by之后进行过滤,使用where子句之后,分组的数据是where子句过滤后的数据集。
mysql> select nation, count(*) as nation_num from user where sex = 0 group by nation;
+----------+------------+
| nation | nation_num |
+----------+------------+
| 维吾尔族 | 1 |
| 汉族 | 1 |
+----------+------------+
2 rows in set (0.00 sec)
mysql> select nation, count(*) as nation_num from user group by nation where nation = '汉族';
error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'where nation = '汉族'' at line 1
vmysql> select nation, count(*) as nation_num from user group by nation having nation = '汉族';
+--------+------------+
| nation | nation_num |
+--------+------------+
| 汉族 | 2 |
+--------+------------+
1 row in set (0.00 sec)
2.4 order by与limit
分组后的数据需要排序可以使用order by,order by子句需要更在having子句之后。
mysql> select nation, count(*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc;
+----------+------------+
| nation | nation_num |
+----------+------------+
| 维吾尔族 | 2 |
| 回族 | 1 |
| 蒙古族 | 1 |
+----------+------------+
3 rows in set (0.00 sec)
对于输出的结果需要指定返回的行数,可以使用limit,limit子句在整个语句的最后。
mysql> select nation, count(*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc limit 2;
+----------+------------+
| nation | nation_num |
+----------+------------+
| 维吾尔族 | 2 |
| 回族 | 1 |
+----------+------------+
2 rows in set (0.00 sec)
2.5 with rollup
在group by子句中,with rollup 可以实现在分组统计数据基础上再进行相同的统计(sum,avg,count…) 比如max():
mysql> select nation, max(height) as nation_num from user group by nation with rollup;
+----------+------------+
| nation | nation_num |
+----------+------------+
| 回族 | 175 |
| 汉族 | 184 |
| 维吾尔族 | 192 |
| 蒙古族 | 177 |
| null | 192 |
+----------+------------+
5 rows in set (0.00 sec)
比如avg():
mysql> select nation, avg(height) as nation_num from user group by nation with rollup;
+----------+--------------------+
| nation | nation_num |
+----------+--------------------+
| 回族 | 175 |
| 汉族 | 182 |
| 维吾尔族 | 180 |
| 蒙古族 | 177 |
| null | 179.33333333333334 |
+----------+--------------------+
5 rows in set (0.00 sec)
比如count():
mysql> select nation, count(*) as nation_num from user group by nation with rollup;
+----------+------------+
| nation | nation_num |
+----------+------------+
| 回族 | 1 |
| 汉族 | 2 |
| 维吾尔族 | 2 |
| 蒙古族 | 1 |
| null | 6 |
+----------+------------+
5 rows in set (0.00 sec)