1 select *, 2 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,3 rank() over (partition by 部门编号 order by 薪水) as rank_24 from 薪水表5 where rank_1 >1 and rank_2 >1;
1 select *2 from (3 select *,4 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,5 rank() over (partition by 部门编号 order by 薪水) as rank_26 from 薪水表7 ) as a8 where a.rank_1 >1 and a.rank_2 >1;
1 select a.部门编号,format(avg(a.薪水),0) as 平均薪水2 from3 (4 select *,5 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,6 rank() over (partition by 部门编号 order by 薪水) as rank_27 from 薪水表8 ) as a9 where a.rank_1 >1 and a.rank_2 >110 group by a.部门编号;
1 select format(avg(a.成绩),2) as 平均成绩2 from3 (4 select *,5 rank() over (order by 成绩 desc) as rank_1,6 rank() over (order by 成绩) as rank_27 from 成绩表8 ) as a9 where a.rank_1 >1 and a.rank_2 >1;