9-8 教室の割り当て

SQLパズル

授業テーブル        教室テーブル
授業名  受講者数     教室番号   席数
------  --------    --------  ----
算盤         100           1    85
数学          80           2    70
算数          70           3    65
DBA           65           4    55
SQL           55           5    55
Unix          50           6    45
Java          40           7    30
C#            30
C++           25

授業テーブルに、受講者数以上の席数を持つ教室を割り当て、出力する

出力結果
授業名  受講者数   教室番号   席数
------  --------  --------  ----
算盤         100      null  null
数学          80         1    85
算数          70         2    70
DBA           65         3    65
SQL           55         4    55
Unix          50         5    55
Java          40         6    45
C#            30         7    30
C++           25      null  null

プログラマのためのSQL第2版の17章[select文]を参考にさせていただきました

データ作成スクリプト

create table 授業(
授業名   char(4),
受講者数 number(3));

insert into 授業 values('算盤',100);
insert into 授業 values('数学', 80);
insert into 授業 values('算数', 70);
insert into 授業 values('DBA' , 65);
insert into 授業 values('SQL' , 55);
insert into 授業 values('Unix', 50);
insert into 授業 values('Java', 40);
insert into 授業 values('C#'  , 30);
insert into 授業 values('C++' , 25);

create table 教室(
教室番号 number(1),
席数     number(2));

insert into 教室 values(1,85);
insert into 教室 values(2,70);
insert into 教室 values(3,65);
insert into 教室 values(4,55);
insert into 教室 values(5,55);
insert into 教室 values(6,45);
insert into 教室 values(7,30);
commit;

SQL

select a.授業名,a.受講者数,b.教室番号,b.席数
from (select 授業名,受講者数,
      Row_Number() over(order by 受講者数 desc) as Rank
      from 授業
      where 受講者数 <= (select max(c.席数) from 教室 c)) a,
     (select 教室番号,席数,
      Row_Number() over(order by 席数 desc) as Rank
      from 教室) b
where a.Rank = b.Rank(+)
union all select 授業名,受講者数,null,null
from 授業
where 受講者数 > (select max(c.席数) from 教室 c)
order by 2 desc;

解説

Row_Number関数で順位付けして、
順位が等しいことを結合条件として、外部結合してます

---