java sql分组函数

作者: 走停2015_iOS开发 | 来源:发表于2020-12-30 09:05 被阅读0次

java sql分组函数
MySQL白菜教程（Level 7）
MySql 分组与统计函数
Java实现GroupBy/分组TopN功能
Oracle_SQL函数-分组函数
Spark语法
Pandas-分组函数和分层索引的展开
记录
SQL集合函数与分组
SQL 学习笔记

优先级：select 分组函数 from 表 where 条件 group by 多个字段 having 条件 order by 顺序
优先级从前向后执行分组函数最后执行

count :计数
sum:求和
avg:平均值
max:最大值
min:最小值

ifnull(字段1 ，当成的数据a) 如果字段1为null 作为a来看待

`1. 找出年龄的总和`
  `select sum(age) from t_user;`
+----------+
| sum(age) |
+----------+
|      215 |
+----------+

2.找出最高年龄
`select max(age) from t_user;`
+----------+
| max(age) |
+----------+
|       36 |

3.找出最底年龄
`select min(age) from t_user;`
+----------+
| min(age) |
+----------+
|       16 |
+----------+

3.找出平均年龄
`select avg(age) from t_user;`
+----------+
| avg(age) |
+----------+
|  21.5000 |
+----------+

4.找出总个数
`select count(*) from t_user;`
`select count(age) from t_user;`
+------------+
| count(age) |
+------------+
|         10 |
+------------+

5.`ifnull(字段1 ，当成的数据a) 如果字段1为null 作为a来看待`
`select ifnull(age,0) from t_user;`
+---------------+
| ifnull(age,0) |
+---------------+
|            36 |
|            17 |
|            17 |
|            18 |
|            12 |
|            19 |
|            17 |
|            32 |
|            18 |
|            25 |
+---------------+

group by: 按照某个字段或某些字段进行分组
having: 是对分组之后的数据再次过滤
1.分组函数一般都会和group by 联合使用并且分组函数都是在group by执行结束后才会执行
2.当没有group by语句的时候整张表的数据自成一组
3.where 条件中不能用分组函数因为先执行where 再执行 group by 然后才执行分组函数

1.找出各个工作年龄的平均值

`select avg(age) from t_user group by job;`
+----------+
| avg(age) |
+----------+
|  20.0000 |
|  24.5000 |
|  15.0000 |
|  27.5000 |
|  17.0000 |
+----------+
`select avg(age),job from t_user group by job;`
+----------+------+
| avg(age) | job  |
+----------+------+
|  20.0000 | AAAA |
|  24.5000 | BBB  |
|  15.0000 | CCC  |
|  27.5000 | DDDD |
|  17.0000 | FFF  |
+----------+------+
`select name,avg(age) from t_user group by job;`
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.t_user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因：name没有参与分组

2.找出每个部门最高的年龄年龄要求大于20 (having)

select max(age),deptno from t_user group by deptno;
+----------+--------+
| max(age) | deptno |
+----------+--------+
|       18 |      2 |
|       36 |      5 |
|       25 |      4 |
|       19 |      1 |
+----------+--------+
select max(age),deptno from t_user group by deptno having max(age)>24;
select max(age),deptno from t_user where age>24 group by deptno ;
+----------+--------+
| max(age) | deptno |
+----------+--------+
|       36 |      5 |
|       25 |      4 |
+----------+--------+

3.找出大约平均年龄的人
1.select avg(age) from t_user
2.select name age from t_user>上面数字age
合并
3.select name age from t_user where age > (select avg(age) from t_user);

select * from t_user where age > (select avg(age) from t_user);
+----+----------+-----+--------+---------+------+
| id | name     | age | gender | content | job  |
+----+----------+-----+--------+---------+------+
|  5 | WeiHua   |  32 |      0 | NULL    | BBB  |
|  6 | ZhangWei |  25 |      1 | NULL    | AAAA |
|  7 | Ann      |  36 |      0 | NULL    | DDDD |
+----+----------+-----+--------+---------+------+
```