sql高频面试题-去除最高最低的平均

面试或者笔试的过程中会设定各种各样的场景，在这些场景下考查我们SQL的查询能力，但是万变不离其宗，业务场景只是一个表现形式，抽象为SQL问题后其实基本上就是几类问题：计算累计、连续，分类TopN等。只要掌握这些问题的解法，并且可以举一反三，并不需要盲目的花费大量的时间精力去刷题，多总结多思考，你就很容易在面试笔试环节脱颖而出了。

案例

#emp_salary 表记录了每个员工的月工资以及所属的部门，包含EmpId(员工ID)、Department(部门名称)、Salary(月工资)。

  select 10001  empId , '市场部' department , 9000 salary into #emp_salary
  union all 
  select 10002  empId , '市场部' department , 8000 salary 
   union all 
  select 10003  empId , '市场部' department , 8600 salary 
   union all 
  select 10004  empId , '市场部' department , 8200 salary 
   union all 
  select 10005  empId , '市场部' department , 8500 salary 
     union all 
  select 10006  empId , '市场部' department , 7500 salary 
       union all 
  select 10007  empId , '运营部' department , 7300 salary 
       union all 
  select 10008  empId , '运营部' department , 7600 salary 
         union all 
  select 10009  empId , '运营部' department , 8600 salary 
           union all 
  select 10010  empId , '运营部' department , 8900 salary 
             union all 
  select 10011  empId , '运营部' department , 8900 salary 
         union all 
  select 10012  empId , '产品部' department , 7600 salary 
         union all 
  select 10013  empId , '产品部' department , 8600 salary 
           union all 
  select 10014  empId , '产品部' department , 8900 salary 
             union all 
  select 10015  empId , '产品部' department , 9000 salary 
  
  select * from #emp_salary

需求：计算除去部门最高工资，和最低工资的平均工资

因为要在每个部门内计算平均工资，但是要去除该部门的最高工资和最低工资，所以我们可以考虑使用窗口函数，按照部门进行分组，但是如何将部门的最高工资和最低工资去除呢？我们可以对分组后的数据按照工资分别顺序和逆序进行排序，排名第一的就分别是该部门最低和最高工资，将这2个排名第一的记录去除就是我们要计算的平均工资。

核心是使用窗口函数RANK分别对工资salary进行升序和降序排列，就获得了该分组内最低和最高的工资，过滤掉这2条记录再对工资salary进行平均avg即可

注意：用rank()排序时，相同的数据的排名也会重复（如下述代码运行结果），即最大值/最小值若有好几个，这几个都会去掉

select *,RANK() over(partition by department order by salary )  sa_asc
,RANK() over(partition by department order by salary desc )  sa_desc
 from #emp_salary

解题sql代码：

select department,AVG(salary) 平均工资
from (
    select *,RANK() over(partition by department order by salary )  sa_asc
    ,RANK() over(partition by department order by salary desc )  sa_desc
    from #emp_salary
)t 
where sa_asc!=1 and sa_desc !=1
group by department