mysql聚合某个字段

转载

mob64ca140f9cec 2024-09-12 22:27:10

文章标签 mysql聚合某个字段 mysql 数据库 sql 聚合函数 文章分类 MySQL 数据库

mysql聚合某个字段_sql

文章目录
1. 聚合函数介绍
1.1 什么是聚合函数
1.2 常用的聚合函数
2. 常用的聚合函数
2.1 AVG()
2.2 SUM()
2.3 MAX()
2.4 MIN()
2.5 COUNT()
2.6 补充
3. GROUP BY
3.1 分组的基本使用
3.2 使用多个列分组
3.3 结论
3.4 WITH ROLLUP
4. HAVING
4.1 HAVING 的使用
4.2 HAVING 与 WHERE 的对比
5. SELECT 的执行过程
5.1 SELECT 语句的完整结构
5.2 SELECT 的执行顺序
6. 聚合函数练习
6.1 题目
6.2 解答

1. 聚合函数介绍

1.1 什么是聚合函数

聚合（或聚集、分组）函数，是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

聚合函数作用于一组数据，并对一组数据返回一个值。

mysql聚合某个字段_mysql_02

1.2 常用的聚合函数

AVG()
SUM()
MAX()
MIN()
COUNT()

2. 常用的聚合函数

2.1 AVG()

只适用于数值类型的字段(或变量)

# 查询所有员工的平均工资
SELECT AVG(salary)
FROM employees;

2.2 SUM()

只适用于数值类型的字段(或变量)

# 查询所有员工的工资总和
SELECT
       SUM(salary),
       # 一共有107个员工
       AVG(salary) * 107
FROM employees;

2.3 MAX()

# 查询员工的最高工资
SELECT MAX(salary)
FROM employees;

MAX()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。

SELECT MAX(last_name),
       MAX(hire_date)
FROM employees;

2.4 MIN()

# 查询员工的最低工资
SELECT MIN(salary)
FROM employees;

MIN()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。

SELECT MIN(last_name),
       MIN(hire_date)
FROM employees;

2.5 COUNT()

计算指定字段在查询结果中出现的个数。

# 查询在全部的107行数据中，有几行拥有员工id这个字段
SELECT COUNT(employee_id)
FROM employees;

COUNT()统计个数，不关注字段的具体取值为多少，只关注某行数据是否具有该字段，该字段是否为空。不为空就加入计数。

SELECT COUNT(employee_id),
       count(salary),
       count(salary * 2)
FROM employees;

COUNT()统计常量

SELECT COUNT(employee_id),
       count(salary),
       count(salary * 2),
       count(1)
FROM employees;

计算表中有多少条记录：

SELECT COUNT(1),
       COUNT(*)
FROM employees;

不建议通过统计某个字段的取值的个数来统计表格的行数，因为有些字段的某些行可能取值为空，而COUNT()不会统计空值。

2.6 补充

AVG()、SUM()和COUNT()一样，会过滤空值。

SELECT AVG(commission_pct),
       SUM(commission_pct) / COUNT(commission_pct),
       # 由于AVG() SUM() COUNT() 都会过滤空值
       # 所以SUM(commission_pct) / 107会比上面的小
       SUM(commission_pct) / 107
FROM employees;

# 计算公司的平均奖金率
# 错误计算方法
# 因为有没有奖金的员工，而AVG()会默认过滤空值
# 所以不能使用这种计算方法
SELECT AVG(commission_pct)
FROM employees;
# 正确的计算方法
# 对于没有奖金的员工commission_pct=0，然后进行统计
SELECT AVG(IFNULL(commission_pct, 0)),
       SUM(commission_pct) / COUNT(*),
       SUM(IFNULL(commission_pct, 0)) / COUNT(*)
FROM employees;

如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

3. GROUP BY

GROUP BY子句可以将表中的数据分成若干组

3.1 分组的基本使用

mysql聚合某个字段_sql_03

# 查询各个部门的员工的平均工资
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

# 查询每个工作的平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

3.2 使用多个列分组

mysql聚合某个字段_数据库_04

# 查询各个(department_id, job_id)的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

分组的字段可以调换顺序，结果一致。

最后结果都为(department_id, job_id)一样的为同一组，只是分组的顺序不一样，一个先根据部门分组再根据工作分组，一个根据工作分组再根据部门分组，最终显示的结果一样。

# 查询各个(job_id, department_id)的平均工资
SELECT job_id, department_id, AVG(salary)
FROM employees
GROUP BY job_id, department_id;

3.3 结论

结论1：
SELECT中出现的非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
结论2：
GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面

3.4 WITH ROLLUP

MySQL中GROUP BY中使用WITH ROLLUP，在所有查询出的分组记录之后增加一条记录，该记录是对所有组在进行一次统计，比如计算每组的平均工资，WITH ROLLUP新加的记录会计算每组合起来总的平均工资，即公司的平均工资。

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

MySQL8.0，不报错，WITH ROLLUP新加记录也会参与排序

SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

4. HAVING

4.1 HAVING 的使用

HAVING子句用于过滤分组之后的数据。

mysql聚合某个字段_sql_05

1、如果过滤条件中使用了聚合函数，或行已经被分组，则必须使用HAVING来替换WHERE。否则，报错。
2、HAVING 必须声明在 GROUP BY 的后面。

# 查询各个部门中最高工资大于10000的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id
# 对分组后的数据进行筛选，选出部门中最高工资大于10000的部门
HAVING max_salary > 10000;

开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

如果没有使用GROUP BY，使用HAVING，则整个表中的所有记录会被当成一组，然后对这一组记录根据HAVING中的条件进行筛选。

SELECT department_id, MAX(salary) max_salary
FROM employees
HAVING max_salary > 10000;

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

# 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
WHERE department_id in (10, 20, 30, 40)
GROUP BY department_id
HAVING max_salary > 10000;

# 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id
# 对记录进行分组后，筛选出部门id为10, 20, 30, 40的组
HAVING department_id in (10, 20, 30, 40) AND
       max_salary > 10000;

1、当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
2、当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

上述两种方式，方式一的执行效率高于方式二，即WHERE的效率会高于HAVING。

4.2 HAVING 与 WHERE 的对比

从适用范围来讲，HAVING 的使用范围更广。
如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING。

WHERE会先过滤掉无用的数据，然后对过滤后的数据进行分组筛选操作，分组筛选操作的数据数小；而HAVING是先对所有的数据进行分组筛选，操作的数据数大，所以WHERE的执行效率要高于HAVING。

mysql聚合某个字段_mysql聚合某个字段_06

5. SELECT 的执行过程

5.1 SELECT 语句的完整结构

#sql92语法：
SELECT ...., ...., ....(存在聚合函数)
FROM ..., ...., ....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ..., ....
HAVING 包含聚合函数的过滤条件
ORDER BY ...., ...(ASC / DESC )
LIMIT ..., ....

#sql99语法：
SELECT ...., ...., ....(存在聚合函数)
FROM ... 
(LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ..., ....
HAVING 包含聚合函数的过滤条件
ORDER BY ...., ...(ASC / DESC )
LIMIT ..., ....

5.2 SELECT 的执行顺序

FROM ..., ...-> 
ON -> 
(LEFT/RIGNT  JOIN) -> 
WHERE -> 
GROUP BY -> 
HAVING -> 
SELECT -> 
DISTINCT -> 
ORDER BY -> 
LIMIT

由于GROUP BY分组操作的执行在WHERE之后，所以不能使用WHERE过滤分组后的数据；
由于SELECT的执行时间在WHERE之后，所以在WHERE中不能使用SELECT中字段的别名。

6. 聚合函数练习

6.1 题目

#1.where子句可否使用组函数进行过滤? 

#2.查询公司员工工资的最大值，最小值，平均值，总和 

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和 

#4.选择具有各个job_id的员工人数 

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE） 

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 

# 8.查询每个工种、每个部门的部门名、工种名和最低工资

6.2 解答

1.where子句可否使用组函数进行过滤?

不能，使用组函数对数据进行过滤只能使用HAVING

2.查询公司员工工资的最大值，最小值，平均值，总和

# 2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),
       MIN(salary),
       AVG(salary),
       SUM(salary)
FROM employees;

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

# 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,
       MAX(salary),
       MIN(salary),
       AVG(salary),
       SUM(salary)
FROM employees
GROUP BY job_id;

4.查询各个job_id的员工人数

# 4.查询各个job_id的员工人数
SELECT job_id,
       COUNT(*)
FROM employees
GROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id,
       MIN(salary) min_salry
FROM employees
# 没有管理者的员工不计算在内
WHERE manager_id IS NOT NULL
GROUP BY manager_id
# 其中最低工资不能低于6000
HAVING min_salry > 6000;

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.department_name,
       d.location_id,
       # 由于有些部门没有员工，由于连接表的方式为左连接，
       # 没有员工的部门也存在一条记录，使用COUNT(*)，统计记录数
       # 没有员工的部门会统计出来1，所以不使用COUNT(*)使用COUNT(e.employee_id)
       # 统计每个部门的员工
       COUNT(e.employee_id),
       # 没有员工的部门，salary为null，计算出来为null
       AVG(e.salary) avg_sal
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
# 出现在SELECT子句中的字段必须也要出现在GROUP BY子句中
GROUP BY d.department_name, d.location_id
ORDER BY avg_sal DESC;

8.查询每个工种、每个部门的部门名、工种名和最低工资

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
# 由于有些部门没有员工所以这些部门的工种以及对应的最低工资为null
SELECT e.job_id,
       d.department_name,
       MIN(e.salary)
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY e.job_id, d.department_name;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：码云 wxpython精美控件

下一篇：Android 怎么设置button字体大小和行高

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯