题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#

SELECT AVG(salary)
FROM (
/1.首先当前员工的最高和最低薪水/
SELECT MAX(salary) AS smax,MIN(salary) AS smin
FROM salaries
WHERE to_date='9999-01-01'
) AS s,salaries
/2.选择当前员工中薪水介于最高和最小之间的员工记录/
WHERE salary < s.smax AND salary > s.smin AND to_date='9999-01-01'

/*方法2.这种做法也是正确的：
SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary NOT IN (SELECT MAX(salary) FROM salaries)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)
*/