postgres分组查询每组前top 10 的人员

查询每个市的前十名人员信息

方法一：

用分组排序row_number() over(）查出前十个，效率较慢

select     *  from tab a where  exists(
select id from (
        select id, row_number() over(partition by city oder by id ) rn  from tab
 ) tab where rn < 51 and  a.id= tab.id )

方法二：

使用迭代函数效率较方法一快

create or replace function fc returns setof tab as
￥￥
 declare cityv character varying(20) ;
 begin
   for cityv in with recursive ta1 as (                                                                           
      select city city_id from tab group by  city                                                                  
      
   )                                                                                                
   select * from ta1
   LOOP
     return query select * from tab where city=cityv order by id limit 10;
   END LOOP;
return;
 
end;
 
￥￥
language plpgsql strict;

执行：

select* from fc();