题目描述
查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后,其他的在职员工的平均工资avg_salary。
思路
1.求单列平均值使用聚合函数avg() ;
2.排除在职(to_date = '9999-01-01' )员工的最大、最小salary,用where to_date = '9999-01-01'筛选在职员工,
然后not in max(salary)、not in min(salary)排除最大、最小值;
3.注意:如果用select min(salary),max(salary) from salaries,输出的是两列,
但是前面是not in,not in只能对单列数值(或单列内多个数值)操作,因此要分开select min(salary)、select max(salary) 。
代码
sql1
select
avg(salary) sav_salary
from salaries
where
salary not in (select min(salary) from salaries where to_date = '9999-01-01')
and salary not in (select max(salary) from salaries where to_date = '9999-01-01') and to_date = '9999-01-01'
标签:salary,01,SQL248,salaries,9999,date,平均工资,select
From: https://www.cnblogs.com/loongnuts/p/17132543.html