查询每个市的前十名人员信息
方法一:
用分组排序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();