某表 tab1:
列1 列2 列3
1 2 3
2 3 3
3 4 3
4 5 4因为列3部分重复,我想给重复的列3加一个后缀
如:列1 列2 列3
1 2 3A
2 3 3B
3 4 3C
4 5 4
或:
列1 列2 列3
1 2 3001
2 3 3002
3 4 3003
4 5 4
请各位高手帮帮忙
列1 列2 列3
1 2 3
2 3 3
3 4 3
4 5 4因为列3部分重复,我想给重复的列3加一个后缀
如:列1 列2 列3
1 2 3A
2 3 3B
3 4 3C
4 5 4
或:
列1 列2 列3
1 2 3001
2 3 3002
3 4 3003
4 5 4
请各位高手帮帮忙
select 1 as col1,2 as col2,3 as col3 from dual union
select 2,3,3 from dual union
select 3,4,3 from dual union
select 4,5,4 from dual
)
select col3,row_number() over (partition by col3 order by col1,col2)
,col3||LPAD(row_number() over (partition by col3 order by col1,col2),3,'0')
from a
where col3 in (select col3 from a group by col3 having count(*)>1)
insert into tab1 values(1,2,3);
insert into tab1 values(2,3,3);
insert into tab1 values(3,4,3);
insert into tab1 values(4,5,4);select a.c1,a.c2,case when b.c3 is not null then a.c3||a.c1 else to_char(a.c3) end c3 from tab1 a,
(select c3,count(*) from tab1
group by c3
having count(*)>1) b
where a.c3=b.c3(+)
select rownum, rownum + 1, '3'
from dual
connect by rownum <= 4;
update t
set c = '4'
where a = 4;select a, b, c, c || case when total_count > 1 then chr(append_x + 64) else '' end your_need
from
(
select a, b, c
,row_number() over(partition by c order by c) append_x
,count(c) over(partition by c) total_count
from t
) x