现有一张表如下COL1 COL2 COL3
AAA FDFS FDF
BBB FDFD EFEF
BBB EFE FDFE
CCC DFD DFDF
CCC DFSF DFSF
CCC FDF DFEW要变为
COL1 COL2 COL3 CN1 CN2
AAA FDFS FDF 1 1
BBB FDFD EFEF 1 2
BBB EFE FDFE 2
CCC DFD DFDF 1 3
CCC DFSF DFSF 2
CCC FDF DFEW 3 如何得出CN1与CN2.这两列都是根据COL1得出的结果。。
AAA FDFS FDF
BBB FDFD EFEF
BBB EFE FDFE
CCC DFD DFDF
CCC DFSF DFSF
CCC FDF DFEW要变为
COL1 COL2 COL3 CN1 CN2
AAA FDFS FDF 1 1
BBB FDFD EFEF 1 2
BBB EFE FDFE 2
CCC DFD DFDF 1 3
CCC DFSF DFSF 2
CCC FDF DFEW 3 如何得出CN1与CN2.这两列都是根据COL1得出的结果。。
decode(row_number() over(partition by col1 order by col2),1,count(*) over (partition by col1),null) cn2
from table
2 BBB B1DF DFDF 1 2
3 BBB FEFE DFDS 2
4 CCC FDFS DFD 1 1
5 DDD DFD DFDS 1 3
6 DDD DFDS DFDS 2
7 DDD FDFS DFSDF 3
出来的结果不对哟,C那里应该是3,D应该是4了,下面是我自己写的,感觉效率不高.SELECT A.*,ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY ROWNUM),B.RN FROM TEST A,
(SELECT ROWNUM RN,RD FROM
(SELECT MIN(ROWID) RD FROM TEST B GROUP BY COL1)) B
WHERE A.ROWID=B.RD(+)
COL1 CN2
AAA 1
BBB 2
BBB
CCC 3
CCC
DDD 4
DDD
DDD
decode(row_number() over(partition by col1 order by col2),1,dense_rank over (order by col1),null) cn2
from table