由一道牛客SQL題引發關于NOT IN 函數、MAX函數、MIN函數的思考

又是随手刷到一道牛客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比較函數和運算符