SQL> select a, b, row_number()over(partition by a order by b) from ( 2 select 1 a, 1 b from dual union all 3 select 1 a, 11 b from dual union all 4 select 1 a, 111 b from dual union all 5 select 2 a, 2 b from dual union all 6 select 2 a, 22 b from dual union all 7 select 2 a, 222 b from dual ); A B ROW_NUMBER()OVER(PARTITIONBYAORDERBYB) ---------- ---------- -------------------------------------- 1 1 1 1 11 2 1 111 3 2 2 1 2 22 2 2 222 3已选择6行。
select a, b, row_number()over(partition by a order by b) c from t
2 select 1 a, 1 b from dual union all
3 select 1 a, 11 b from dual union all
4 select 1 a, 111 b from dual union all
5 select 2 a, 2 b from dual union all
6 select 2 a, 22 b from dual union all
7 select 2 a, 222 b from dual ); A B ROW_NUMBER()OVER(PARTITIONBYAORDERBYB)
---------- ---------- --------------------------------------
1 1 1
1 11 2
1 111 3
2 2 1
2 22 2
2 222 3已选择6行。
看到liuyxit(初学Oracle) ( )的解答,我笑了,不过这个的确是楼主没说清楚的缘故。