求一SQL语句

[yjdfff]

现有一张表如下

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得出的结果。。

[hebo2005]

select COL1 ,COL2 ,  COL3 ,row_number() over(partition by col1 order by col2) cn1,
decode(row_number() over(partition by col1 order by col2),1,count(*) over (partition by col1),null) cn2
from table

[yjdfff]

[Quote=引用 1 楼 hebo2005 的回复:]
SQL codeselect COL1 ,COL2 , COL3 ,row_number() over(partition by col1 order by col2) cn1,
decode(row_number() over(partition by col1 order by col2),1,count(*) over (partition by col1),null) cn2
from table
[/Quote]


1 AAA FDF FDSF 1 1
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(+)

[welyngj]

up

[yjdfff]

可能是我没写明白,CN2得出的是COL1中分组的累加数，不是COL1中分组的个数

[lovejoy_chen]

能解释CN1，CN2是怎么根据COL1得出的吗

[yjdfff]

CN2是根把COL1的分组得到的如：
COL1 CN2
AAA 1
BBB 2
BBB
CCC 3
CCC
DDD 4
DDD
DDD

[hebo2005]

select COL1 ,COL2 ,  COL3 ,row_number() over(partition by col1 order by col2) cn1,
decode(row_number() over(partition by col1 order by col2),1,dense_rank over (order by col1),null) cn2
from table

[hebo2005]

主要你的示例没说清楚，只能瞎猜了

[yjdfff]

[Quote=引用 8 楼 hebo2005 的回复:]
主要你的示例没说清楚，只能瞎猜了
[/Quote]

呵呵，谢谢!!!!