MySQL聚合函数

聚合函数
MySQL8.0版本提供的聚合函数类型一共有19个，本文结合具体实例介绍其中较常用的前9个。剩余聚合函数可点击函数名跳转到MySQL官网查看具体解释。

名字	描述
AVG()	返回参数的平均值
MAX()	返回最大值
MIN()	返回最小值
SUM()	返回总和
COUNT()	返回返回的行数的计数
COUNT(DISTINCT)	返回多个不同值的计数
GROUP_CONCAT()	返回串联字符串
JSON_ARRAYAGG()	将结果集作为单个JSON数组返回
JSON_OBJECTAGG()	将结果集作为单个JSON对象返回
BIT_AND()	按位返回 AND
BIT_OR()	按位返回OR
BIT_XOR()	按位返回异或
STD()	返回总体标准差
STDDEV()	返回总体标准差
STDDEV_POP()	返回总体标准差
STDDEV_SAMP()	返回样本标准偏差
VAR_POP()	返回总体标准方差
VAR_SAMP()	返回样本方差
VARIANCE()	返回总体标准方差

数据准备

-- 员工表
CREATE TABLE emp (
	id INT PRIMARY KEY,-- 员工id
	name VARCHAR (50),-- 员工姓名
	join_date DATE,-- 入职日期
	salary DECIMAL (7, 2),-- 工资
	bonus DECIMAL (7, 2),-- 绩效奖金
	dept VARCHAR(50)-- 所属部门
);

-- 添加员工
INSERT INTO emp (id, name, join_date, salary, bonus, dept)
VALUES
	(1001, '孙悟空', '2000-12-17', '8000.00', NULL, '学工部'),
	(1002, '卢俊义', '2001-02-20', '16000.00', '3000.00', '销售部'),
	(1003, '林冲', '2001-02-22', '12500.00', '5000.00', '销售部'),
	(1004, '唐僧', '2001-04-02', '29750.00', NULL, '学工部'),
	(1005, '李逵', '2001-09-28', '12500.00', '14000.00', '销售部'),
	(1006, '宋江', '2001-05-01', '28500.00', NULL, '销售部'),
	(1007, '刘备', '2001-09-01', '24500.00', NULL, '教研部'),
	(1008, '猪八戒', '2007-04-19', '30000.00', NULL, '学工部'),
	(1009, '罗贯中', '2001-11-17', '50000.00', NULL, '教研部'),
	(1010, '吴用', '2001-09-08', '15000.00', '6000.00', '销售部'),
	(1011, '沙僧', '2007-05-23', '11000.00', NULL, '学工部'),
	(1012, '李逵', '2001-12-03', '9500.00', NULL, '销售部'),
	(1013, '小白龙', '2001-12-03', '30000.00', NULL, '学工部'),
	(1014, '关羽', '2002-01-23', '13000.00', NULL, '教研部');

AVG()
例：查询所有员工的平均工资，查询结果保留两位小数

-- AVG()返回参数的平均值，ROUND(参数, 保留位数)
SELECT
	ROUND(AVG(salary), 2) '平均工资' 
FROM
	emp;

MAX()
例：查询工资最高的员工信息

-- MAX() 子查询
-- 不能直接将MAX(salary)作为条件 否则会报Invalid use of group function聚合函数不可用的错误
-- 需要先从emp表中查出最高工资，再查询对应员工信息
SELECT
	*
FROM
	emp
WHERE
	emp.salary = (SELECT MAX(salary) FROM emp);
-- 错误示例
SELECT
	* 
FROM
	emp 
WHERE
	emp.salary = MAX(salary);

MIN()
例：查询工资最低的员工信息

-- MIN() 子查询(同上MAX)
SELECT
	* 
FROM
	emp 
WHERE
	emp.salary = (SELECT MIN(salary) FROM emp);

SUM()
例：查询所有员工的工资总和

-- SUM()，返回指定参数列的值总和
SELECT
	SUM(salary) '工资总和' 
FROM
	emp;

COUNT()
例：查询拿到绩效奖金的员工人数

-- COUNT() 
-- 如果没有匹配的行，COUNT() 将返回0
-- COUNT(*) 略有不同，它返回检索到的行数的计数，无论它们是否包含NULL
SELECT
	COUNT(bonus) '获得绩效奖金的人数'
FROM
	emp;

COUNT(DISTINCT)
例：查询emp表中的员工共来自几个部门

-- COUNT(DISTINCT) DISTINCT去除重复记录
-- 错误示例（不去除重复记录，查询结果为14）
SELECT
	COUNT(dept) '部门总数'
FROM
	emp;
-- 正确
SELECT
	COUNT(DISTINCT dept) '部门总数'
FROM
	emp;

GROUP_CONCAT()
例：查询emp表中各部门员工姓名

-- GROUP_CONCAT()
-- 函数返回一个字符串结果
SELECT 
	dept '部门',
	GROUP_CONCAT(name) '员工姓名'
FROM 
	emp
GROUP BY
	dept;

JSON_ARRAYAGG()
例：查询emp表中各部门员工姓名

-- JSON_ARRAYAGG(col or expr)，参数可以是 列名 也可以是一个 表达式
-- 将结果集聚合为单个 JSON 数组，其元素由行组成。此数组中元素的顺序未定义
SELECT
	dept '部门',
	JSON_ARRAYAGG(name) '部门员工姓名'
FROM 
	emp
GROUP BY
	dept;

JSON_OBJECTAGG()
例：查询emp表中所有的员工及其薪资，返回姓名:薪资的 JSON 对象

-- JSON_ARRAYAGG(key, value)，
-- 将两个列名或表达式作为参数，其中第一个用作键，第二个用作值，并返回一个包含键值对的 JSON 对象
SELECT
	JSON_OBJECTAGG(name, salary) '员工:薪资'
FROM 
	emp
GROUP BY
	id;