一个简单sql，记住高级的，可惜忘记了初级的

需求是找出每个部门薪水最高的三个职员。

dep 部门
emp 人员
sal 薪水

基本处理方式

with tmp_t0 as (
  select '开发部' as dep,'张一' as emp, 1000::int8 as sal union all
  select '开发部' as dep,'张二' as emp, 2000::int8 as sal union all
  select '开发部' as dep,'张三' as emp, 3000::int8 as sal union all
  select '开发部' as dep,'张四' as emp, 4000::int8 as sal union all
  select '测试部' as dep,'李一' as emp, 2000::int8 as sal union all
  select '测试部' as dep,'李二' as emp, 8000::int8 as sal union all
  select '测试部' as dep,'李三' as emp, 4000::int8 as sal union all
  select '运维部' as dep,'王一' as emp, 500::int8 as sal 
)
select t0.dep,
       t0.emp,
       t0.sal
  from tmp_t0 t0
 where 1=1
   and ( select count(1)
           from tmp_t0 t1
          where 1=1
            and t0.dep=t1.dep
            and t1.sal >= t0.sal
        ) <=3
 order by t0.dep,
          t0.sal desc

分析函数处理

with tmp_t0 as (
  select '开发部' as dep,'张一' as emp, 1000::int8 as sal union all
  select '开发部' as dep,'张二' as emp, 2000::int8 as sal union all
  select '开发部' as dep,'张三' as emp, 3000::int8 as sal union all
  select '开发部' as dep,'张四' as emp, 4000::int8 as sal union all
  select '测试部' as dep,'李一' as emp, 2000::int8 as sal union all
  select '测试部' as dep,'李二' as emp, 8000::int8 as sal union all
  select '测试部' as dep,'李三' as emp, 4000::int8 as sal union all
  select '运维部' as dep,'王一' as emp, 500::int8 as sal 
)
select t00.dep,
       t00.emp,
       t00.sal
from (
    select t0.dep,
           t0.emp,
           t0.sal,
           rank() over (partition by t0.dep order by t0.sal desc) as rk
      from tmp_t0 t0
     where 1=1
     ) t00
where 1=1
  and t00.rk <=3
 order by t00.dep,

第一个基本处理方式居然没有想到，一看到需求就想到用分析函数。
懂得高级的，也不能忘记初级的。