浅谈MySQL中的group by(2)-木庄网络博客

语句：

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)

2.3 having使用

对group by分组后的数据还需要再次过滤，就必须使用having子句。group by子句后使用where子句MySQL服务器会抛出异常

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

此时只需要将上面where子句替换成having子句即可，having子句支持所有的where操作符，通俗的说where子句能用的地方只有替换成having就可以在group by子句后使用了

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)

到此这篇关于浅谈MySQL中的group by的文章就介绍到这了,更多相关MySQL中的group by内容请搜索

更多Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容