Mysql分组查询配合聚合函数
Mysql是我们最常使用的数据库之一，因此对于开发新人而言，必须掌握好其方方面面的知识，今天和大家分享的就是Mysql分组查询配合聚合函数相关内容，一起来看看吧。
配合聚合函数（系统函数）
COUNT()
MAX()
MIN()
AVG()
SUM()
注意：GROU BY配合聚合函数得到分组详情
查询编号、性别、得到用户详情按照性别分组
mysql> SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+
| id | sex | GROUP_CONCAT(username) |
+----+--------+---------------------------------+
| 1 | 男 | 张三,rose,king,ring,章子怡 |
| 4 | 女 | long,queen,blek,张三丰,lily |
| 11 | 保密 | john,test1 |
+----+--------+---------------------------------+
3 rows in set (0.07 sec)
查询ProID、性别详情、注册时间详情、以及用户名详情安装prod来分组
mysql> SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
-> FROM cms_user GROUP BY proId;
+-------+-------------------------------+------------------------+--------------------------------------------------------+
|proId|GROUP_CONCAT(username)|GROUP_CONCAT(sex)|GROUP_CONCAT(regTime) |
+-------+-------------------------------+------------------------+--------------------------------------------------------+
|1|张三,blek,test1| 男,女,保密|1419811708,1419818708,1419811708 |
|2|ring,张三丰,rose,lily,john | 男,女,男,女,保密 | 1419815708,1419812708,1419821708,1419831708,1419841708 |
|3|queen,章子怡| 女,男| 1419861708,1419813708 |
| 4 | long | 女 | 1419814708 |
| 5 | king | 男 | 1419817708 |
+-------+-------------------------------+------------------------+--------------------------------------------------------+
5 rows in set (0.00 sec)
立起来显示加一个\G（得到用户详情、性别，注册时间）
mysql> SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
-> FROM cms_user GROUP BY proId\G;
* 1. row *
proId: 1
GROUP_CONCAT(username): 张三,blek,test1
GROUP_CONCAT(sex): 男,女,保密
GROUP_CONCAT(regTime): 1419811708,1419818708,1419811708
* 2. row *
proId: 2
GROUP_CONCAT(username): ring,张三丰,rose,lily,john
GROUP_CONCAT(sex): 男,女,男,女,保密
GROUP_CONCAT(regTime): 1419815708,1419812708,1419821708,1419831708,1419841708
* 3. row *
proId: 3
GROUP_CONCAT(username): queen,章子怡
GROUP_CONCAT(sex): 女,男
GROUP_CONCAT(regTime): 1419861708,1419813708
* 4. row *
proId: 4
GROUP_CONCAT(username): long
GROUP_CONCAT(sex): 女
GROUP_CONCAT(regTime): 1419814708
* 5. row *
proId: 5
GROUP_CONCAT(username): king
GROUP_CONCAT(sex): 男
GROUP_CONCAT(regTime): 1419817708
5 rows in set (0.00 sec)
查看cms_user表的记录
mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username | password | email | regTime | face | proId | age | sex |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| 1 | 张三 | zhangsan | user@qq.com | 1419811708 | user.jpg | 1 | 18 | 男 |
| 2 | 张三丰 | zhangsanfeng | user@qq.com | 1419812708 | user.jpg | 2 | 18 | 女 |
| 3 | 章子怡 | zhangsan | user@qq.com | 1419813708 | user.jpg | 3 | 18 | 男 |
| 4 | long | long | user@qq.com | 1419814708 | user.jpg | 4 | 18 | 女 |
| 5 | ring | ring | user@qq.com | 1419815708 | user.jpg | 2 | 18 | 男 |
| 6 | queen | queen | user@qq.com | 1419861708 | user.jpg | 3 | 18 | 女 |
| 7 | king | king | user@qq.com | 1419817708 | user.jpg | 5 | 18 | 男 |
| 8 | blek | blek | user@qq.com | 1419818708 | user.jpg | 1 | 18 | 女 |
| 9 | rose | rose | user@qq.com | 1419821708 | user.jpg | 2 | 18 | 男 |
| 10 | lily | lily | user@qq.com | 1419831708 | user.jpg | 2 | 18 | 女 |
| 11 | john | john | user@qq.com | 1419841708 | user.jpg | 2 | 18 | 保密 |
| 12 | test1 | test1 | user@qq.com | 1419811708 | user.jpg | 1 | NULL | 保密 |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)
更新年龄字段
mysql> UPDATE cms_user SET age=11 WHERE id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=21 WHERE id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=33 WHERE id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=44 WHERE id=4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=25 WHERE id=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=77 WHERE id=6;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=56 WHERE id=7;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=88 WHERE id=8;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=12 WHERE id=9;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=32 WHERE id=10;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE cms_user SET age=65 WHERE id=11;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看cms_user表记录
mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username | password | email | regTime | face | proId | age | sex |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| 1 | 张三 | zhangsa | user@qq.com | 1419811708 | user.jpg | 1 | 11 | 男 |
| 2 | 张三丰 | zhangsanfeng | user@qq.com | 1419812708 | user.jpg | 2 | 21 | 女
| 3 | 章子怡 | zhangsan | user@qq.com | 1419813708 | user.jpg | 3 | 33 | 男 |
| 4 | long | long | user@qq.com | 1419814708 | user.jpg | 4 | 44 | 女 |
| 5 | ring | ring | user@qq.com | 1419815708 | user.jpg | 2 | 25 | 男 |
| 6 | queen | queen | user@qq.com | 1419861708 | user.jpg | 3 | 77 | 女 |
| 7 | king | king | user@qq.com | 1419817708 | user.jpg | 5 | 56 | 男 |
| 8 | blek | blek | user@qq.com | 1419818708 | user.jpg | 1 | 88 | 女 |
| 9 | rose | rose | user@qq.com | 1419821708 | user.jpg | 2 | 12 | 男 |
| 10 | lily | lily | user@qq.com | 1419831708 | user.jpg | 2 | 32 | 女 |
| 11 | john | john | user@qq.com | 1419841708 | user.jpg | 2 | 65 | 保密 |
| 12 | test | test1 | user@qq.com | 1419811708 | user.jpg | 1 | NULL | 保密 |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)
查询编号，性别、用户详情以及组中总人数按照性别分组
mysql> SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+------------+
| id | sex | users | totalUsers |
+----+--------+---------------------------------+------------+
| 1 | 男 | 张三,rose,king,ring,章子怡 | 5 |
| 4 | 女 | long,queen,blek,张三丰,lily | 5 |
| 11 | 保密 | john,test1 | 2 |
+----+--------+---------------------------------+------------+
3 rows in set (0.02 sec)
统计表中所有记录（使用*）
mysql> SELECT COUNT(*) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
| 12 |
+------------+
1 row in set (0.01 sec)
统计表中所有记录（使用指定字段）
mysql> SELECT COUNT(id) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
| 12 |
+------------+
1 row in set (0.00 sec)
查看记录
mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username | password | emai | regTime | face | proId | age | sex |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| 1 | 张三 | zhangsa | user@qq.com | 1419811708 | user.jpg | 1 | 11 | 男 |
| 2 | 张三丰 | zhangsanfeng | user@qq.com | 1419812708 | user.jpg | 2 21 | 女 |
| 3 | 章子怡| zhangsa | user@qq.com | 1419813708 | user.jpg | 3 | 33 | 男 |
| 4 | long | long | user@qq.com | 1419814708 | user.jpg | 4 | 44 | 女 |
| 5 | ring | ring | user@qq.com | 1419815708 | user.jpg | 2 | 25 | 男 |
| 6 | queen | queen | user@qq.com | 1419861708 | user.jpg | 3 | 77 | 女 |
| 7 | king | king | user@qq.com | 1419817708 | user.jpg | 5 | 56 | 男 |
| 8 | blek | blek | user@qq.com | 1419818708 | user.jpg | 1 | 88 | 女 |
| 9 | rose | rose | user@qq.com | 1419821708 | user.jpg | 2 | 12 | 男 |
| 10 | lily | lily | user@qq.com | 1419831708 | user.jpg | 2 | 32 | 女 |
| 11 | john | john | user@qq.com | 1419841708 | user.jpg | 2 | 65 | 保密 |
| 12 | test1 | test1 | user@qq.com | 1419811708 | user.jpg | 1 | NULL | 保密 |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)
Count（字段）不统计null值
mysql> SELECT COUNT(age) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
| 11 |
+------------+
1 row in set (0.00 sec)
查询编号、性别、用户名详情、组中总人数、组中最大年龄、最小年龄、平均年龄、以及年龄总和（集合函数的使用）
mysql> SELECT id,sex,GROUP_CONCAT(username),
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age,
-> AVG(age) AS avg_age,
-> SUM(age) AS sum_age
-> FROM cms_user
-> GROUP BY sex;
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
| id | sex | GROUP_CONCAT(username) | totalUsers | max_age | min_age | avg_age | sum_age |
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
| 1 | 男 | 张三,rose,king,ring,章子怡 | 5 | 56 | 11 | 27.4000 | 137 |
| 4 | 女 | long,queen,blek,张三丰,lily | 5 | 88 | 21 | 52.4000 | 262 |
| 11 | 保密 | john,test1 | 2 | 65 | 65 | 65.0000 | 65 |
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
3 rows in set (0.05 sec)
配合WITH ROLLUP记录上面所有记录的总和
在语句的末尾加上with rollup，在语句的末尾统计记录的总和。
with rollup，在语句末尾添加一条记录
mysql> SELECT id,sex,GROUP_CONCAT(username),
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age,
-> AVG(age) AS avg_age,
-> SUM(age) AS sum_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP;
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
| id | sex | GROUP_CONCAT(username) | totalUsers | max_age | min_age | avg_age | sum_age |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
|1| | 张三,rose,king,ring,章子怡 | 5 | 56 | 11 | 27.4000 | 137 |
| 4| 女 | long,queen,blek,张三丰,lily | 5 | 88 | 21 | 52.4000 | 262 |
|11| 保密 | john,test1 | 2 | 65 | 65 | 65.0000 | 65 |
| 11 | NULL | 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1 | 12 | 88 | 11 | 42.1818 | 464 |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
立起来显示
mysql> SELECT id,sex,GROUP_CONCAT(username),
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age,
-> AVG(age) AS avg_age,
-> SUM(age) AS sum_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP\G;
* 1. row *
id: 1
sex: 男
GROUP_CONCAT(username): 张三,rose,king,ring,章子怡
totalUsers: 5
max_age: 56
min_age: 11
avg_age: 27.4000
sum_age: 137
* 2. row *
id: 4
sex: 女
GROUP_CONCAT(username): long,queen,blek,张三丰,lily
totalUsers: 5
max_age: 88
min_age: 21
avg_age: 52.4000
sum_age: 262
* 3. row *
id: 11
sex: 保密
GROUP_CONCAT(username): john,test1
totalUsers: 2
max_age: 65
min_age: 65
avg_age: 65.0000
sum_age: 65
* 4. row *
id: 11
sex: NULL
GROUP_CONCAT(username): 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1
totalUsers: 12
max_age: 88
min_age: 11
avg_age: 42.1818
sum_age: 464
4 rows in set (0.00 sec)
在语句的末尾加上with rollup
ERROR:
No query specified
mysql> SELECT id,sex,GROUP_CONCAT(username),
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP;
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
| id | sex | GROUP_CONCAT(username) | totalUsers | max_age | min_age |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
| 1 | 男 | 张三,rose,king,ring,章子怡 | 5 | 56 | 11 |
| 4 | 女 | long,queen,blek,张三丰,lily | 5 | 88 | 21 |
| 11 | 保密 | john,test1 | 2 | 65 | 65 |
| 11 | NULL | 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1 | 12 | 88 | 11 |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
4 rows in set (0.00 sec)
在语句的末尾加上with rollup
mysql> SELECT id,sex,
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP;
+----+--------+------------+---------+---------+
| id | sex | totalUsers | max_age | min_age |
+----+--------+------------+---------+---------+
| 1 | 男 | 5 | 56 | 11 |
| 4 | 女 | 5 | 88 | 21 |
| 11 | 保密 | 2 | 65 | 65 |
| 11 | NULL | 12 | 88 | 11 |
+----+--------+------------+---------+---------+
4 rows in set (0.00 sec)
统计年龄总和
mysql> SELECT id,sex,
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age
->SUM(age)AS sum_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP;
+----+--------+------------+---------+---------+
| id | sex | totalUsers | max_age | min_age | sum_age|
+----+--------+------------+---------+---------+
| 1 | 男 | 5 | 56 | 11 | 137|
| 4 | 女 | 5 | 88 | 21 | 262|
| 11 | 保密 | 2 | 65 | 65 | 65|
| 11 | NULL | 12 | 88 | 11 | 464 |
+----+--------+------------+---------+---------+
4 rows in set (0.00 sec)
有疑问加站长微信联系（非本文作者）
