10-74 Oracle8での順位による制御

SQLパズル

table_test
OYA_NO  KO_NO
------  -----
A         KA1
A         KA2
A         KA3
B         KB1
C         KC1
C         KC2
D         KD1
D         KD2
D         KD3
D         KD4
D         KD5

OYA_NOごとに、
2行を1行にまとめて出力する(Oracle8のSQLで行う)

出力結果
OYA_NO  Left  Right
------  ----  -----
A        KA1    KA2
A        KA3   null
B        KB1   null
C        KC1    KC2
D        KD1    KD2
D        KD3    KD4
D        KD5   null

データ作成スクリプト

create table table_test(
oya_no char(1),
ko_no  char(3));

insert into table_test values('A','KA1');
insert into table_test values('A','KA2');
insert into table_test values('A','KA3');
insert into table_test values('B','KB1');
insert into table_test values('C','KC1');
insert into table_test values('C','KC2');
insert into table_test values('D','KD1');
insert into table_test values('D','KD2');
insert into table_test values('D','KD3');
insert into table_test values('D','KD4');
insert into table_test values('D','KD5');
commit;

SQL

col oya_no for a6
col Left   for a6
col Right  for a6

select oya_no,
max(decode(mod(cnt,2),1,ko_no)) as Left,
max(decode(mod(cnt,2),0,ko_no)) as Right
from (select t1.oya_no,t1.ko_no,count(t2.ko_no) as cnt
        from table_test t1,table_test t2
       where t1.oya_no = t2.oya_no
         and t1.ko_no >= t2.ko_no
       group by t1.oya_no, t1.ko_no)
group by oya_no,ceil(cnt/2)
order by oya_no,ceil(cnt/2);

解説

自己結合を使って、順位を求めてます

---