又是随手刷到一道牛客SQL题，这次是👉查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。此题其实不算难，一开始掌柜想到的解题思路如下：👇

  • 首先找到最大最小工资，然后剔除这个数值；
  • 接下来筛选当前日期为’9999-01-01’ 的平均工资数据即可。
    但是这题其实又有点坑🤔，因为题意并没有明显说👉要你在剔除最大最小工资那里也是筛选当前日期为’9999-01-01’ ！！！ 这个条件，所以如果你一开始没有get到这个隐藏点就按掌柜一开始的思路来提交查询就会报错。。。

按照牛客这个正解来看的思路应该是：

  • 首先剔除当前日期在’9999-01-01’ 的最大最小值；
  • 接下来筛选当前日期为’9999-01-01’ 的数据；
  • 最后合并这两个筛选条件求平均工资即可。

具体解法掌柜就不写了，再来看看此题主要考察知识点：👉NOT IN 函数、聚合函数以及子查询的用法。

看过掌柜之前题解的朋友应该都知道，掌柜习惯自己解完题再去看看题解区的优秀解法，然后就看到有好几个朋友有下面👇这样的一个疑问：


然后掌柜就去试了一下，发现如果剔除最大最小工资的时候，把MIN(salary)和MAX(salary）一起写再筛选确实会报错！！！

  • 那么问题就来了，
    🤔为什么不能把MAX(salary)，MIN(salary)一起写；必须要分开写才能得到所谓的正解？

    掌柜又去MySQL官方文档查看关于聚合函数里面MAX()和MIN()函数的用法，然后看到这样一段文字：

    仔细看这个示例，查询是可以一起用的MIN()和MAX()函数的！！！ 接着掌柜在线测试牛客这题如果只查询最大最小工资的话，确实是可以这样写的：

  • 掌柜继续测试，这次按牛客那个要求来；结果得到的工资就是这样的：

    发现没，如果合并最大最小值作为筛选条件放在子查询那里，再次查询工资就会出现👉只剔除了最大值，但是最小值是还在的！！！

  • 那么问题又来了！？👉为什么单独查询就可以找到最大最小值，但是放在子查询里面就只剔除了部分（最大值，保留了最小值），最后出错了？？

    掌柜就继续翻看官方文档，然后又让我翻到这样一段文字：

    官方文档里面写的是子查询后面跟着NOT IN等价于<> ALL，翻译一下如果在牛客这题里这样用👇：

SELECT AVG(salary) FROM salaries WHERE salary NOT IN (SELECT MAX(salary), MIN(salary) FROM salaries WHERE to_date = '9999-01-01') AND to_date = '9999-01-01';

其实是等价于

SELECT AVG(salary) FROM salaries WHERE salary <> ALL (SELECT MAX(salary), MIN(salary) FROM salaries WHERE to_date = '9999-01-01')

重点来了❗❗❗其中salary <> ALL (MAX(salary), MIN(salary))等价于 salary<> MAX(salary) OR salary<> MIN(salary)，所以当单个工资同这个集合进行比较的时候，根据OR的逻辑语法：👉只需其中一个逻辑语句是true！！！就会返回查询结果！！！

因此，只要不等于最大工资就都会返回到最后的查询里面，于是就有这样的错误结果：

  • 根据题意，正确的解法应该是这样写😁： 
SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
AND to_date = '9999-01-01';

总结：
MAX、MIN可以一起在MySQL的查询中使用，但是当作为子查询条件的时候要警惕！！！尤其是子查询包含比较运算符👉NOT IN的时候！！！

参考资料：
MySQL子查询
MySQL比较函数和运算符