如下：
SQL> with t as
2(
3select 'A' col_a from dual
4union all
5select 'A' col_a from dual
6union all
7select 'B' col_a from dual
8union all
9select 'B' col_a from dual
10union all
11select 'C' col_a from dual
12union all
13select 'C' col_a from dual
14)
15select col_a,
16 dense_rank() over(order by col_a) new_col
17from t
18/
COL_ANEW_COL
----- ----------
A
1
A
1
B
2
B
2
C
3
C
3
6 rows selected
版主的方法是最简单明了的。
WITH t AS (
SELECT 'A' c1 from dual
UNION ALL SELECT 'A'from dual
UNION ALL SELECT 'B'from dual
UNION ALL SELECT 'B'from dual
UNION ALL SELECT 'C'from dual
UNION ALL SELECT 'C'from dual
)
, t2 AS (SELECT c1,LEAD(c1) OVER(PARTITION BY c1 ORDER BY c1) c2 FROM t)
SELECT c1,SUM(CASE WHEN c1=c2 THEN 1 ELSE 0 END) OVER (ORDER BY c1)c3 FROM t2
