说明:
row_number() over ([partition col1] [order by col2])
rank() over ([partition col1] [order by col2])
dense_rank() over ([partition col1] [order by col2])
它们都是根据col1字段分组,然后对col2字段进行排序,对排序后的每行生成一个行号,这个行号从1开始递增
col1、col2都可以是多个字段,用‘,‘分隔
select *,dense_rank() over (partition by name orderby score asc) as rank from t;
业务实例:
统计每个学科的前二名
select * from (select *, row_number() over(partition by name orderby score desc) as rank from t )t where rank <=2;select *,row_number() over () as rank from t rank <=3;
select area, barnd, yuan, row_number() over (partition by area orderby yuan desc) as rank fromorderwhere rank <=3;