select a.name,b.name,a.salary,row_number() over (partition by b.name order by a.salary) as rn from A left join B on A.id = B.aid
解析:row_number() over (partition by b.name order by a.salary) as rn
1.简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。
2.row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)