Mysql学习之分组查询配合聚合函数

maiziedu · · 3360 次点击 · 开始浏览    置顶
这是一个创建于 的主题,其中的信息可能已经有所发展或是发生改变。

  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)   原文链接:http://www.maiziedu.com/wiki/mysql/function/

有疑问加站长微信联系(非本文作者)

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

3360 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传