select *

  from (select p.province_name,

               p.province_code,

               c.city_name,

               c.city_code,

               c.city_id,

               ROW_NUMBER() OVER(PARTITION BY p.province_name order BY c.city_id DESC) AS r

          from hs_basic_province p

          left join hs_basic_city c

            on c.province_id = p.province_id) t

 where t.r <3;

Oracle中row_number() over(partition by xxx order by xxx)的用法

row_number() over(partition by a order by b)

上面的意思就是将查询结果按照a字段分组（partition），

然后组内按照b字段排序，至于asc还是desc，可自行选择，

然后为每行记录返回一个rownumber用于标记顺序