SQL248 #平均工资#
平均工资
https://www.nowcoder.com/practice/95078e5e1fba4438b85d9f11240bc591
select
avg(salary) avg_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
salary not in (
select
min(salary)
from
salaries
where
to_date = '9999-01-01'
)
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) 。
