配合聚合函数（系统函数）

最新推荐文章于 2022-05-13 14:52:47 发布
最新推荐文章于 2022-05-13 14:52:47 发布
阅读量368 收藏
点赞数
分类专栏： Java
配合聚合函数（系统函数）

SQL中提供的聚合函数可以用来统计、求和、求最值等等。

分类：

–COUNT：统计行数量
–SUM：获取单个列的合计值
–AVG：计算某个列的平均值
–MAX：计算列的最大值
–MIN：计算列的最小值



注意：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)
꧁꫞弑༒魂꫞꧂
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL常用聚合函数详解
09-10
下面小编就为大家带来一篇MySQL常用聚合函数详解。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
MySQL聚合函数
weixin_45966674的博客
08-28 323
这三条数据 都有 user_id 所以 都查到了 字段查法好处在于 可能有一些数据少了某个字段 这样就可以根据字段找出 那些数据指定的字段是有值的 过滤掉没有指定字段的数据。这里 我们求了user_ging字段的合 但有个条件 要 user_ging大于30的数据才能参与。然后我们去一下user_ging的平均值 不要纠结意思 这个字段我做的时候也没想过什么意思。然后我们求这张表所有数据的user_ging字段的合。然后我们来取 user_ging的最大值。...
高德地图实现聚合点功能实例
觉醒法师的专栏
05-13 7498
在使用高德地图时，有时会出现海量数据展示，这里就不得不使用聚合点功能，减少页面初始化过程中加载过多数据而导致卡顿现象。
jQuery聚合函数实例
10-24
主要介绍了jQuery聚合函数,实例分析了jQuery中聚合函数的使用方法,需要的朋友可以参考下
MySql 中聚合函数增加条件表达式的方法
09-09
主要介绍了MySql 中聚合函数增加条件表达式的方法,需要的朋友可以参考下
聚合函数 查询汇总信息
04-12
统计数据库学生总人数。 查询第一学期的总学时。 查询学号s1101004的学生第一学期考试的总成绩。查询学号s1101004的学生第一学期所有考试的平均分。 查询2013年3月22日科目“C#语言和数据库技术”的最高分，最低分，...
聚合接口对接,加油卡,流量充值,话费充值
【雪涛】的专栏
01-07 1301
<?php /** * 聚合接口模型. * * @version TS4.5 * @name JuheModel * * @author Foreach */ class Juhe { public $appkey_config = array( 'tel'=>'appkey', 'flow'=>'appkey', ...
地图API自定义点聚合
huaziqq的专栏
05-21 2809
地图API自定义点聚合详见http://www.hjlib.org/cd/tiles/zoom/index.html
MySQL中使用group_concat()函数数据被截取（有默认长度限制），谨慎！
静如雪的博客
07-30 4140
最近在工作中遇到一个问题： SELECT lr_product.store_id,group_concat(concat_ws(',', lr_product.id )) FROM `lr_product`inner join `lr_b_store` on lr_b_store.id=lr_product.store_id where `lr_b_store`.is_del=0 and `...
高德地图API注意事项
weixin_30342209的博客
02-14 326
注意事项 高德地图采用 GCJ-02 坐标系，即火星坐标系。与 GPS 坐标系，即 WGS-84 坐标系存在一定的偏移量，如果您采集到的坐标数据为GPS坐标，可以先通过AMap.ConvertFrom进行坐标偏移后进行制图或者使用，才能够和高德的底图完全匹配。 经纬度的有效范围为经度-180度到+180度，纬度大约-85度到+85度。 创建地图对象后，开始加载地图资源，地图加载完成后触发compl...
Mysql在select子句中聚集函数和普通属性一起写出现的一个有趣的问题！
李树花开，风中摇曳
11-15 4036
一个很有趣的问题，我现在有一张products表，表中数据如下： [为了简洁，截去了prod_desc的描述信息]现在我执行：select vend_id, prod_price from products where prod_price >= 10;结果如下： 没有问题，然后我们执行： select vend_id, count(*) as num_prods, prod_price
SQL数据库语言基础之SqlServer系统函数、聚合集合函数【大总结】
Viewinfinitely的博客
11-20 2026
关于SQL数据库语言基础，SqlServer中的系统函数、聚合集合函数的大总结。
提高你开发效率的十五个 Visual Studio 使用技巧
weixin_33909059的博客
03-05 424
转自http://www.cnblogs.com/rond/archive/2012/03/05/2380367.html 提高你开发效率的十五个 Visual Studio 使用技巧 相信做开发的没有不重视效率的。开发C#，VB的都知道，我们很依赖VS，或者说，我们很感谢VS。能够对一个IDE产生依赖，说明这个IDE确实有它的独特之处。无容置疑，VS是一个非常强大的IDE，它支持多语...
基于高德地图实现Marker聚合效果
热门推荐
沐风的博客
11-20 1万+
最近,项目有了新的需求,要求地图上的标签点实现聚合效果,单纯的marker已经无法满足大量数据展示的情况,聚合效果成为大势所趋。  目前,网上提供基于高德marker聚合的思路大致差不多,处于雏形阶段。高德官方也提供了关于聚合的解决方案,对于缓存和加载效率都做了一些处理,为我们后面的定制奠定了基础,本文就在高德官方提供的方案基础上做一些定制化。笔者经过思考后,还是觉得将篇幅分为上下两部分,前篇主要涉
聚合函数配合窗口函数使用
最新发布
05-14
聚合函数和窗口函数可以配合使用，用于对分组数据进行分析和统计。 例如，我们可以使用窗口函数来计算每个分组内的排名，然后再使用聚合函数来计算每个分组内排名为前三的数据的平均值。 具体操作步骤如下： 1. ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值