直接上代码~
求每个省份人口数量前三的城市
select
province
,city
,population
from
(
select
province
,city
,population
,row_number() over(partition by province order by population desc) rnk
from table
) tmp
where rnk <= 3
;
思路分析
1.分组:"每个省份" partition by province
2.排序:"人口" order by population desc
3.过滤:"人口前三" rnk<=3
4.指标:"城市"