正确的MySQL查询语句是:
正确的MySQL查询语句是:
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);
SELECT AVG(salary) AS avg_salary FROM salaries WHERE salary > MIN(salary) AND salary < MAX(salary) AND to_date = '9999-01-01';
SELECT ((SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2)) avg_salary FROM salaries WHERE to_date = '9999-01-01';
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';
-- 现在找出, 除了在职员工 求离职员工 去除最高和最低工资求其它员工的平均工资 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'; -- 思路 -- 查询平均工资使用AVG函数 as 起别名 叫avg_salary 使用where判断条件 工资最大条件符合的不出现 和 工资最小的符合条件的不出现 和 符合 to_date = '9999-01-01
select (sum(salary) / count(emp_no)) as avg_salary from salaries where to_date = '9999-01-01' and salary not in ((select min(salary) as min from salaries where to_date = '9999-01-01') union (select max(salary) as max from salaries where to_date = '9999-01-01'))