5-14 order by句で相関サブクエリ

SQLパズル

型番テーブル
PNO     KataNO
------  ------
000001  %%%%%%
000002  ______
000003  %%%%%%
000004  aaaaaa

マスタテーブル
KataMaster  Maker
----------  -------
%%%%%%0825  marker9
______0925  marker2
aaaaaa1025  marker5

KataMasterの先頭6桁は、KataNO（型番）
KataMasterの後ろ4桁は、訂符（更新記録）

KataNOは必ずKataMasterの先頭6桁とマッチする。
KataMasterの先頭6桁が同じであれば必ず同一のMakerである。

型番テーブルのPNOをマスタテーブルのMakerの昇順で出力する。

出力結果
PNO
------
000002
000004
000001
000003

データ作成スクリプト

create table 型番(
PNO    char(6),
KataNO char(6),
primary key (PNO));

create table マスタ(
KataMaster char(10),
Maker      char(7));

insert into 型番(PNO,KataNO) values('000001','%%%%%%');
insert into 型番(PNO,KataNO) values('000002','______');
insert into 型番(PNO,KataNO) values('000003','%%%%%%');
insert into 型番(PNO,KataNO) values('000004','aaaaaa');
insert into マスタ(KataMaster,Maker) values('%%%%%%0825','marker9');
insert into マスタ(KataMaster,Maker) values('______0925','marker2');
insert into マスタ(KataMaster,Maker) values('aaaaaa1025','marker5');
commit;

SQL

--■■■substr関数を使用する方法■■■
select PNO from 型番 a
order by (select b.Maker from マスタ b
           where substr(b.KataMaster,1,6) = a.KataNO);

--■■■Like演算子を使用する方法その1■■■
select PNO from 型番 a
order by (select b.Maker from マスタ b
           where b.KataMaster
            Like replace(replace(replace(a.KataNO,'\','\\'),'%','\%'),'_','\_') || '%' escape '\');

--■■■Like演算子を使用する方法その2(Oracle10g以降)■■■
select PNO from 型番 a
order by (select b.Maker from マスタ b
           where b.KataMaster
            Like RegExp_Replace(a.KataNO,'([\%_])','\\\1') || '%' escape '\');

--■■■正規表現を使用する方法(Oracle10g以降)■■■
select PNO from 型番 a
order by (select b.Maker from マスタ b
           where RegExp_Substr(b.KataMaster,'^.{6}') = a.KataNO);

解説

order by句で、スカラー問い合わせの相関サブクリを使うことができます。
Like演算子を使う場合は、特殊文字をエスケープする必要があります。

Like演算子のメタキャラのエスケープ