我有一个视图,生产这样的结果集合,如何可以添加序号
PK(字段名)
10001
10001
10001
10002
10002
10003
10004
想要的结果
序号 PK
1 10001
2 10001
3 10001
1 10002
2 10002
1 10003
1 10004在视图中不能用ROWNUM ,ROWID
PK(字段名)
10001
10001
10001
10002
10002
10003
10004
想要的结果
序号 PK
1 10001
2 10001
3 10001
1 10002
2 10002
1 10003
1 10004在视图中不能用ROWNUM ,ROWID
pk_column
from table_name;
---------- ---------- --------------------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER已选择14行。
with tb as(
select '10001' id from dual union all
select '10001' from dual union all
select '10001' from dual union all
select '10002' from dual union all
select '10002' from dual union all
select '10003' from dual union all
select '10004' from dual)
select rank() over(partition by id order by rownum) rn,
id
from tb RN ID
------- -----
1 10001
2 10001
3 10001
1 10002
2 10002
1 10003
1 10004