10-97 複数あれば連番を付与

SQLパズル

IDTable
ID1  ID2  ID3
---  ---  ---
AA1  BB1  CC1
AA1  BB2  CC2
AA1  BB3  CC3
AA1  BB4  CC4
AA1  BB5  CC4
AA2  BB6  CC5
AA3  BB7  CC6
AA4  BB8  CC7

ID1ごとに連番をふる
ただし、同一のID1が複数存在するレコードのみとする

出力結果
ID1      ID2  ID3
-------  ---  ---
AA1_001  BB1  CC1
AA1_002  BB2  CC2
AA1_003  BB3  CC3
AA1_004  BB4  CC4
AA1_005  BB5  CC4
AA2      BB6  CC5
AA3      BB7  CC6
AA4      BB8  CC7

データ作成スクリプト

create Table IDTable(
ID1 char(3),
ID2 char(3),
ID3 char(3));

insert into IDTable values('AA1','BB1','CC1');
insert into IDTable values('AA1','BB2','CC2');
insert into IDTable values('AA1','BB3','CC3');
insert into IDTable values('AA1','BB4','CC4');
insert into IDTable values('AA1','BB5','CC4');
insert into IDTable values('AA2','BB6','CC5');
insert into IDTable values('AA3','BB7','CC6');
insert into IDTable values('AA4','BB8','CC7');
commit;

SQL

select ID1 ||
case when count(*) over(partition by ID1) > 1
     then '_' || to_char(Row_Number() over(partition by ID1 order by ID2,ID3),'FM000')
     end as ID1,
ID2,ID3
from IDTable;

解説

case式とcount関数を組み合わせてます

---