题解 | #平均工资#

-- 先找到最大最小值，not exists方案
SELECT AVG(t.salary) FROM
salaries t
WHERE NOT EXISTS(
    SELECT 1 FROM (SELECT max(salary) mx,min(salary) mn FROM salaries s WHERE s.to_date = '9999-01-01') temp
    WHERE t.salary = temp.mx OR t.salary = temp.mn
)
AND t.to_date = '9999-01-01'
;
-- 先找到最大最小值，find_in_set方案,该方案可能会出错
SELECT AVG(t.salary) FROM
salaries t,(SELECT CONCAT(max(salary),',',min(salary)) str FROM salaries s WHERE s.to_date = '9999-01-01') temp
WHERE 
FIND_IN_SET(t.salary,str)=0
AND to_date = '9999-01-01'