在子查询上使用row_number over返回的rn都是1

如果直接查询表是没问题，但是业务需求是按汇总后的amount排序，所以有一个from子查询，请问有没有什么方法汇总后求topN select id,province,amount,rn from( select id,province,amount, row_number() over(partition by province order by amount desc ) as rn from ( select id,province,sum(amount) amount from mytable group by id,province )m )a where rn<=5 ; 返回结果： 1> (true,id001,浙江,1505.66,1) 2> (true,id001,其他,3384.91,1) 7> (true,id001,北京,365.87,1) 3> (true,id001,天津,310.38,1) 7> (false,id001,北京,365.87,1) 7> (true,id001,北京,676.25,1) 7> (false,id001,北京,676.25,1) 7> (true,id001,北京,978.14,1) 7> (true,id001,广东,329.25,1) 7> (false,id001,广东,329.25,1) 如果直接查询表是没问题： select id,province,amount,rn from( select id,province,amount, row_number() over(partition by province order by amount desc ) as rn from mytable )a where rn<=5 ; 7> (true,id001,北京,310.38,2) 6> (true,id001,湖北,344.34,1) 8> (true,id001,山东,348.11,1) 3> (true,id001,四川,7283.02,2) 7> (true,id001,北京,301.89,3) 3> (false,id001,四川,1128.3,2) 8> (true,id001,重庆,310.38,3) 3> (true,id001,四川,1128.3,3) 6> (true,id001,上海,647.55,1) 3> (false,id001,四川,310.38,3) 6> (false,id001,上海,310.38,1) 7> (true,id001,广东,329.25,1) 3> (true,id001,四川,310.38,4) 8> (true,id001,重庆,1618.87,1) 6> (true,id001,上海,310.38,2)*来自志愿者整理的flink邮件归档