SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN
2 from (select '1' a, '2' b, '1' c
3 from dual
4 union all
5 select '1', '2', '2'
6 from dual
7 union all
8 select '1', '3', '3'
9 from dual
10 union all
11 select '1', '3', '4'
12 from dual
13 union all
14 select '1', '4', '5' from dual
15 union all
16 select '1','3','5'from dual) G
17 ;
A B C ROWN
- - - ----------
1 2 2 1
1 2 1 2
1 3 5 1
1 3 4 2
1 3 3 3
1 4 5 1
6 rows selected
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码