mysql5.7.20-取每个分组AVG值中的最小值(MIN)-分组求均值后，查询均值最小值行信息

目录

一、需求

二、遇到的问题

三、具体实现

方法一：

方法二：

---

一、需求

实际中常会遇到分组求均值后，查询均值中最小值行信息的操作。举个栗子：

有员工薪资统计表如下：

其中，

em_id：员工ID

salary：薪资

dep_id：员工所在部门ID

现想查询平均工资最低（或最高）的部门的ID以及对应的平均工资值。

二、遇到的问题

惯性做法：

首先，按dep_id分组，然后AVG,得到各个部门的薪资平均值：

SELECT AVG(salary) AS salary,dep_id FROM salary GROUP BY dep_id;

然后，通过SELECT 结合MIN从上面的查询结果中查出最小平均值和对应的ID即可

SELECT
	MIN(salary),
	dep_id
FROM
	(
		SELECT
			AVG(salary) AS salary,
			dep_id
		FROM
			salary
		GROUP BY
			dep_id
	) AS t_avgsal;

结果如下：

提示错误：[Err] 1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 't_avgsal.dep_id'; this is incompatible with sql_mode=only_full_group_by

按提示先解决错误提示问题：

在MySQL5.7.5后，默认开启了ONLY_FULL_GROUP_BY，所以导致了一些SQL无法正常执行。

在MySQL5.7版本之后对group by进行了优化。他默认启动改进之后的版本启动了ONLY_FULL_GROUP_BY模式。

官方解释：ONLY_FULL_GROUP_BY是MySQL数据库提供的一个sql_mode，通过sql_mode来保证

SQL语句“分组求最值”合法性的检查。这种模式采用了与Oracle、DB2等数据库的处理方式。即不允许select target list

中出现语义不明确的列。

可以先尝试解决sql_mode=only_full_group_by的问题，看以上查询脚本会输出什么内容：

查看sql_mode值

select @@sql_mode;

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我们暂时将 ONLY_FULL_GROUP_BY去掉

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

现在再执行之前的查询脚本，可以输出了：

但是输出内容不对

最平均值对应的dep_id应该是4，而不是1.

出现这样的问题，其实，是我们的SQL不规范造成的，因为group by 之后，返回的一些数据是不确定的，不确定返回字段可以使用ANY_VALUE(column_name)。

三、具体实现

方法一：

SELECT
	AVG(salary) AS salary,
	dep_id
FROM
	salary
GROUP BY
	dep_id
ORDER BY
	salary ASC
LIMIT 1

如果求最大值，可以通过调整排序方式，来实现。

方法二：

SELECT
	salary AS salary,
	dep_id AS dep_id
FROM
	(
		SELECT
			AVG(salary) AS salary,
			dep_id AS dep_id
		FROM
			salary
		GROUP BY
			dep_id
	) AS t_a
WHERE
	salary = (
		SELECT
			MIN(salary) AS salary
		FROM
			(
				SELECT
					AVG(salary) AS salary,
					dep_id AS dep_id
				FROM
					salary
				GROUP BY
					dep_id
			) AS t_b
	);