7-44 最長一致の原則で結合

SQLパズル

Table744    マスタ744
ID          ID      Val
------      ------  ---
abcdef      abcdef  1
abc123      ab      2
123abc      %%      3
%%aaaa      %%%%%   4

Table744を、マスタ744と、
IDが前方一致することを条件として外部結合して、
ID、Valを出力する。

ただし、前方一致するIDが複数あった場合は、
最長のIDと結合する(最長一致の原則で結合)

出力結果
ID      Val
------  ----
abcdef  1
abc123  2
123abc  null
%%aaaa  3

データ作成スクリプト

create table Table744(ID char(6));
insert into Table744 values('abcdef');
insert into Table744 values('abc123');
insert into Table744 values('123abc');
insert into Table744 values('%%aaaa');

create table マスタ744(
ID  varchar2(6),
Val number(1));

insert into マスタ744 values('abcdef',1);
insert into マスタ744 values('ab'    ,2);
insert into マスタ744 values('%%'    ,3);
insert into マスタ744 values('%%%%%' ,4);
commit;

SQL

select ID,
(select distinct
Last_Value(b.Val) over(order by Length(b.ID)
Rows between Unbounded Preceding and Unbounded Following)
   from マスタ744 b
  where a.ID
   Like replace(replace(replace(b.ID,'\','\\'),'%','\%'),'_','\_') || '%' escape '\') as Val
from Table744 a;

解説

distinctを使って、Last_Value関数の結果の重複を排除して、
スカラー問い合わせになるようにしてます。

---