10-197 SQL99構文にこだわったクエリ

SQLパズル

masterTable
ID  name
--  ---------
 1  Example 1
 2  Example 2

tranTable1      tranTable2
ID  f1          ID  f2
--  --          --  --
 1  a            1  x
 1  b            1  y
 2  aa           1  z
 2  bb           2  ww
 2  cc
 2  dd

tranTable1とtranTable2を、
IDごとに、f1およびf2の昇順でマージした結果と、
masterTableをIDをキーにして内部結合した結果を出力する。

出力結果
NAME       F1    F2
---------  ----  ----
Example 1  a     x
Example 1  b     y
Example 1  null  z
Example 2  aa    ww
Example 2  bb    null
Example 2  cc    null
Example 2  dd    null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table masterTable(ID,name) as
select 1,'Example 1' from dual union
select 2,'Example 2' from dual;

create table tranTable1(ID,f1) as
select 1,'a'  from dual union
select 1,'b'  from dual union
select 2,'aa' from dual union
select 2,'bb' from dual union
select 2,'cc' from dual union
select 2,'dd' from dual;

create table tranTable2(ID,f2) as
select 1,'x'  from dual union
select 1,'y'  from dual union
select 1,'z'  from dual union
select 2,'ww' from dual;

SQL

col f1 for a10
col f2 for a10

select c.name,a.F1,b.f2
     from (select ID,f1,Row_Number() over(partition by ID order by f1) as Rank from tranTable1) a
full join (select ID,f2,Row_Number() over(partition by ID order by f2) as Rank from tranTable2) b
       on (a.ID = b.ID and a.Rank = b.Rank)
     join masterTable c on (c.ID= nvl(a.ID,b.ID))
order by c.name,a.f1,b.f2;

解説

Row_Number関数の結果をキーとして完全外部結合した結果と
内部結合させてます。

4-1 インラインビューで完全外部結合

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
10gR1だと動かないようです。

SQL> select version from v$instance;

VERSION
-----------------
10.1.0.2.0

SQL> create table tranTable1(f1) as
  2  select 'a' from dual union
  3  select 'b' from dual ;

表が作成されました。

SQL> create table tranTable2(f2) as
  2  select 'x' from dual union
  3  select 'y' from dual union
  4  select 'z' from dual ;

表が作成されました。

SQL> col f1 for a10
SQL> col f2 for a10
SQL>
SQL> select *
  2       from (select f1,Row_Number() over(order by f1) as Rank from tranTable1) a
  3  full join (select f2,Row_Number() over(order by f2) as Rank from tranTable2) b
  4         on (a.Rank = b.Rank);

F1  ■RANK■F2■RANK
----■----■--■----
a   ■   1■x ■   1
b   ■   2■y ■   2
null■null■z ■null
null■null■y ■null
null■null■x ■null

■■■10gR2の場合■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

SQL> create table tranTable1(f1) as
  2  select 'a' from dual union
  3  select 'b' from dual ;

表が作成されました。

SQL> create table tranTable2(f2) as
  2  select 'x' from dual union
  3  select 'y' from dual union
  4  select 'z' from dual ;

表が作成されました。

SQL> col f1 for a10
SQL> col f2 for a10
SQL>
SQL> select *
  2       from (select f1,Row_Number() over(order by f1) as Rank from tranTable1) a
  3  full join (select f2,Row_Number() over(order by f2) as Rank from tranTable2) b
  4         on (a.Rank = b.Rank);

F1  ■RANK■F2■RANK
----■----■--■----
a   ■   1■x ■   1
b   ■   2■y ■   2
null■null■z ■   3

---