2-3-1 自己結合と相関サブクエリ

SQLパズル

SeqTable
No
--
 1
 2
 3
 4
 5
 6
 7
 8
 9
11

SeqTableのNo(数値型)の空き番号の中で、最小の番号を取得する。
ただし、SeqTableにレコードがない場合は、1を取得する。

データ作成スクリプト

create table SeqTable(
No Number(4),
primary key (No));

begin
    for i in 1..1000 loop
        if i != 10 then
            insert into SeqTable(No) values(i);
        end if;
    end loop;
    commit;
end;
/

SQL

--■■■min関数とnvl関数を使用■■■
select nvl(min(No)+1,1) as No from SeqTable a
where not exists(select 1 from SeqTable b
                  where b.No = a.No + 1);

--■■■min関数とcase式を使用■■■
select
case when exists(select 1 from SeqTable)
     then (select min(No)+1 from SeqTable a
            where not exists(select 1 from SeqTable b
                              where b.No = a.No + 1))
     else 1 end as No
from dual;

--■■■分析関数を使用1■■■
select nvl(min(No+1),1) as No
from (select No,Lead(No) over(order by No) as LeadNo
        from SeqTable)
where No+1 != LeadNo
   or LeadNo is null;

--■■■分析関数を使用2(10g以降)■■■
select nvl(min(No+1),1) as No
from (select No,Lead(No) over(order by No) as LeadNo
        from SeqTable)
where lnnvl(No+1 = LeadNo);

--■■■分析関数を使用3■■■
select nvl(max(No),0)+1 as No
from (select No,Row_Number() over(order by No) as Rn
        from SeqTable)
where No = Rn;

解説

次のレコードが存在しないことをwhere句で条件として使用して、
条件を満たすレコードの中で最小のNoを、min関数を使って取得してます。

自己結合と相関サブクエリを組み合わせれば、
JavaやC#のfor文のように、データを走査することが可能となり、
SQLで、構造化定理の繰り返しに相当するアルゴリズムが実現できます。

2-3-27 最小の空き番号を取得その1
2-3-28 最小の空き番号を取得その2

---

Joe Celko氏のサイト
CodeZine：HAVING句の力
構造化プログラミング(PDF)

---