CodeZineのMySQLの記事(シーズン2)の元原稿   第2回 再帰With句のサンプル集

第3回 MySQL8.0でSQLパズルの問題を解く


今回のテーマ

MySQL8.0で、『SQLパズル 第2版』の問題を解きます。


動作確認環境

MySQL 8.0.15


目次

17. 人材紹介会社
18. ダイレクトメール
21. 飛行機と飛行士
27. 等しい集合を見つける
48. 非グループ化
53. テーブルを列ごとに折りたたむ
56. ホテルの部屋番号
59. 期間を結合する
62. レポートの整形
63. 連続的なグルーピング
66. 数独パズル
99. 参考資料


パズル17. 人材紹介会社

Skillsテーブル
ID   Skill
---  --------
100  会計
100  在庫管理
100  製造
200  会計
200  在庫管理
300  製造
400  在庫管理
400  製造
500  会計
500  製造

下記の条件を満たすIDを出力する。

Skill = '製造' のレコードが存在し、かつ、Skill = '在庫管理' のレコードが存在する。
または
Skill = '会計' のレコードが存在する。

出力結果
ID
---
100
200
400
500

-- ■■■ データ作成スクリプト ■■■
create table Skills(
ID    int,
Skill char(4),
primary key(ID,Skill));

insert into Skills values(100,'会計'),
                         (100,'在庫管理'),
                         (100,'製造'),
                         (200,'会計'),
                         (200,'在庫管理'),
                         (300,'製造'),
                         (400,'在庫管理'),
                         (400,'製造'),
                         (500,'会計'),
                         (500,'製造');

-- ■■■ 解1 Case式とSum関数を組み合わせる方法 ■■■
select ID
  from Skills
group by ID
having sum(case Skill when '製造'     then 1 else 0 end) > 0
   and sum(case Skill when '在庫管理' then 1 else 0 end) > 0
    or sum(case Skill when '会計'     then 1 else 0 end) > 0
order by ID;

-- ■■■ 解2 max関数を使う方法 ■■■
select ID
  from Skills
group by ID
having max(Skill = '製造') and max(Skill = '在庫管理')
    or max(Skill = '会計')
order by ID;

MySQLのBoolean型は、C++の論理型のように、trueが1、falseが0なので、
max関数で存在肯定命題をチェックでき、min関数で全称肯定命題をチェックできます。

SQLのイメージは下記となります。group by IDで赤線を引いてます。



パズル18. ダイレクトメール

Consumersテーブル
ConName  Address  Con_ID  Fam
-------  -------  ------  ----
Bob      AAAA          1  null  ←削除対象行
Joe      BBBB          3  null  ←削除対象行
Mark     CCCC          5  null
Mary     AAAA          2     1
Vickie   BBBB          4     3
Wayne    DDDD          6  null

Fam列がnullで、かつ、Address列が同じ行が存在したら、削除する。

-- ■■■ データ作成スクリプト ■■■
create table Consumers(
ConName char(6),
Address char(4),
Con_ID  int,
Fam     int);

insert into Consumers values('Bob'   ,'AAAA',1,null),
                            ('Joe'   ,'BBBB',3,null),
                            ('Mark'  ,'CCCC',5,null),
                            ('Mary'  ,'AAAA',2,   1),
                            ('Vickie','BBBB',4,   3),
                            ('Wayne' ,'DDDD',6,null);

-- ■■■ 解1 削除可能なビュー(DeletableView)もどきを使う方法 ■■■
with DelView as(
select ConName
  from (select ConName,Fam,count(*) over(partition by Address) as Cnt
          from Consumers) tmp
 where Fam is null
   and Cnt > 1)
delete from Consumers
 where ConName in(select ConName from DelView);

With句で定義したビューに対してDelete文を実行すると
MySQL 8.0では文法エラーになりますので、In述語で定義したビューを参照してます。

参考リンク MySQL Bugs: #88397: CTE with window function don't work with update/delete as expected.

-- ■■■ 解2 having句でチェックする方法 ■■■
with tmp as(
select Address
  from Consumers
group by Address
having count(*) > 1)
delete from Consumers
 where Fam is null
   and Address in(select Address from tmp);

having句でチェックする方法もあります。SQLのイメージは下記となります。
group by Addressで赤線を引いてます。



パズル21. 飛行機と飛行士

PilotSkillsテーブル     Hangarテーブル
Pilot    Plane          Plane
-------  --------       --------
Celko    ペリカン       ジャビィ
Higgins  ハンター       ハンター
Higgins  ファルコ       ファルコ
Higgins  ペリカン
Jones    ハンター
Jones    ファルコ
Smith    ジャビィ
Smith    ハンター
Smith    ファルコ
Wilson   ジャビィ
Wilson   ハンター
Wilson   ファルコ
Wilson   ラップル

Hangarテーブルの全レコードのPlaneを持つPilotを出力する。
(Hangarテーブルが空集合のケースは考えません)

出力結果
Pilot
------
Smith
Wilson

-- ■■■ データ作成スクリプト ■■■
create table PilotSkills(
Pilot char(7),
Plane char(4),
primary key(Pilot, Plane));

insert into PilotSkills values('Celko'  ,'ペリカン'),
                              ('Higgins','ハンター'),
                              ('Higgins','ファルコ'),
                              ('Higgins','ペリカン'),
                              ('Jones'  ,'ハンター'),
                              ('Jones'  ,'ファルコ'),
                              ('Smith'  ,'ジャビィ'),
                              ('Smith'  ,'ハンター'),
                              ('Smith'  ,'ファルコ'),
                              ('Wilson' ,'ジャビィ'),
                              ('Wilson' ,'ハンター'),
                              ('Wilson' ,'ファルコ'),
                              ('Wilson' ,'ラップル');

create table Hangar(Plane char(16) primary key);
insert into Hangar values('ジャビィ'),
                         ('ハンター'),
                         ('ファルコ');

-- ■■■ 解1 not in述語で差集合を求める方法 ■■■
select distinct Pilot
  from PilotSkills a
 where not exists(select b.Plane from Hangar b
                   where b.Plane
           not in(select c.Plane from PilotSkills c
                   where c.Pilot = a.Pilot))
order by Pilot;

not in述語で求めた差集合が、空集合かで、包含関係をチェックしてます。
数学的には、下記の同値な命題をふまえてます。なお、X∩Not(Y) は not in述語で実現してます。

X∩Not(Y) = 空集合 ⇔ X⊆Y

-- ■■■ 解2 Window関数のcount関数で件数を求めてから結合する方法 ■■■
select b.Pilot
from (select Plane,count(*) over() as cnt
        from Hangar) a
Join PilotSkills b
  on a.Plane = b.Plane
group by b.Pilot,a.cnt
having count(*) = a.cnt
order by b.Pilot;

最初にインラインビューでWindow関数のcount関数を使って、
Hangarテーブルの行数を列別名cntとして求めてます。

次に、Planeが等しいことを条件として内部結合(等価結合)してます。
そして、group by b.Pilot,a.cntでグループ化し、having count(*) = a.cntによって、
内部結合した結果の件数がHangarテーブルの行数と同じであることを抽出条件としてます。

数学的には、下記の同値な命題をふまえてます。なお、X∩Y は、内部結合で実現してます。

X∩Y = X ⇔ X⊆Y

SQLのイメージは下記となります。
Window関数のcount関数に対応する黄緑線を引き、
Planeが等しいことを条件とした内部結合(等価結合)に対応するベン図をイメージしながら
青線や紫線などを引き、group by b.Pilot,a.cntに対応する赤線を引いてます。



パズル27. 等しい集合を見つける

『達人に学ぶ SQL徹底指南書 第2版』の、
「等しい部分集合を見つける」(189ページ)で扱われている、同じ問題を解きます。

SupPartsテーブル
sup  part
---  --------
A    ボルト
A    ナット
A    パイプ
B    ボルト
B    パイプ
C    ボルト
C    ナット
C    パイプ
D    ボルト
D    パイプ
E    ヒューズ
E    ナット
E    パイプ
F    ヒューズ

数も種類もまったく同じpartを取り扱うsupの組み合わせを求めます。

出力結果
s1  s2
--  --
A   C
B   D

-- ■■■ データ作成スクリプト ■■■
create table SupParts(
sup  char(1),
part char(4),
primary key(sup,part));

insert into SupParts values('A','ボルト'),
                           ('A','ナット'),
                           ('A','パイプ'),
                           ('B','ボルト'),
                           ('B','パイプ'),
                           ('C','ボルト'),
                           ('C','ナット'),
                           ('C','パイプ'),
                           ('D','ボルト'),
                           ('D','パイプ'),
                           ('E','ヒューズ'),
                           ('E','ナット'),
                           ('E','パイプ'),
                           ('F','ヒューズ');

-- ■■■ 解1 内部結合後の件数を調べる方法 ■■■
with tmp as(
select sup,part,count(*) over(partition by sup) as cnt
  from SupParts)
select a.sup as s1,b.sup as s2
  from tmp a Join tmp b
    on a.sup < b.sup
   and a.cnt = b.cnt
   and a.part = b.part
group by a.sup,b.sup,a.cnt
having count(*) = a.cnt
order by a.sup,b.sup;

まず、Window関数のcount関数でsupごとの件数を求めた結果を、仮想表tmpとしてます。

次に、supが自分より大きいこと、件数が等しいこと、partが等しいことを条件として自己内部結合させてます。
With句は、select文の結果同士を自己結合させる際に使うと便利です。

そして、GroupBy句でsupの組み合わせでグループ化して、having count(*) = a.cntで、
内部結合によって件数が減らなかったsupの組み合わせを出力対象としてます。

SQLのイメージは、下記となります。
仮想表tmpのselect文のcount(*) over(partition by sup)に対応する赤線と黄緑線を引いてから、
仮想表tmp同士の、supが自分より大きいこと、件数が等しいこと、partが等しいことを条件とした
自己内部結合をイメージしてます。



数学の集合では、集合の相等性を調べる公式として、下記が有名ですが、

(A ⊆ B ) かつ (A ⊇ B) ⇔ (A = B)

下記も成立します。

(集合Aと集合Bの要素数が等しい) かつ (A ⊆ B) ⇔ (A = B)

集合Aと集合Bが両方とも空集合の場合は、自明ですし、
集合Aと集合Bが両方とも空集合でない場合は、
要素数が等しくて包含関係が成立するのは、A=Bの場合しかないからです。

要素数は、Window関数のcount関数を使えば求まりますし、
包含関係は、要素が等しいことを条件として内部結合して、
要素数が減らなかったかを調べれば分かります。

-- ■■■ 解2 group_concat関数を使う方法 ■■■
with tmp as(
select sup,group_concat(part order by part) as PartList
  from SupParts
group by sup)
select a.sup,b.sup
  from tmp a Join tmp b
    on a.sup < b.sup
   and a.PartList = b.PartList
order by a.sup,b.sup;

group_concat関数にOrderByを指定しておいて、連結した文字列同士が等しいかをチェックしてもいいです。


パズル48. 非グループ化

Inventoryテーブル
goods  pieces
-----  ------
AAAAA       0
BBBBB       1
CCCCC       2
DDDDD       3

商品(piece)1個につき、1行として出力する。

出力結果
goods  piece
-----  -----
BBBBB      1
CCCCC      1
CCCCC      1
DDDDD      1
DDDDD      1
DDDDD      1

-- ■■■ データ作成スクリプト ■■■
create table Inventory(
goods  char(5) primary key,
pieces int);

insert into Inventory values('AAAAA',0),
                            ('BBBBB',1),
                            ('CCCCC',2),
                            ('DDDDD',3);

-- ■■■ 解1 再帰With句を使う方法 ■■■
with recursive rec(goods,pieces,cnt) as(
select goods,pieces,1
  from Inventory
 where 1 <= pieces
union all
select goods,pieces,cnt+1
  from rec
 where cnt+1 <= pieces)
select goods,1 as piece
  from rec
order by goods;

非再帰項でpiecesが1以上である行を抽出して、再帰項でcntのインクリメントを繰り返してます。
非再帰項の段階での、SQLのイメージは下記となります。非再帰項による木の根の作成をイメージしています。



再帰項の段階での、SQLのイメージは下記となります。再帰項による木のノードの作成をイメージしています。



再帰With句の処理は、非再帰項と再帰項で2段階に分けてイメージすると分かりやすいです。


パズル53. テーブルを列ごとに折りたたむ

Foobarテーブル
lvl  color  length  width  hgt
---  -----  ------  -----  ----
  1  RED         8     10    12
  2  null     null   null    20
  3  null        9     25    25
  4  BLUE     null     67  null
  5  GRAY     null   null  null

lvlの降順に、各列の値を見ていって、それぞれの最初の非null値をまとめて出力する。

出力結果
color  length  width  hgt
-----  ------  -----  ---
GRAY        9     67   25

-- ■■■ データ作成スクリプト ■■■
create table Foobar(
lvl    int primary key,
color  char(4),
length int,
width  int,
hgt    int);

insert into Foobar values(1,'RED' ,   8,  10,  12),
                         (2,null  ,null,null,  20),
                         (3,null  ,   9,  25,  25),
                         (4,'BLUE',null,  67,null),
                         (5,'GRAY',null,null,null);

-- ■■■ 解1 非nullな最大のソートキーを求める方法 ■■■
with tmp as(
select lvl,color,length,width,hgt,
max(case when color  is not null then lvl end) over() as colorTarget,
max(case when length is not null then lvl end) over() as lengthTarget,
max(case when width  is not null then lvl end) over() as widthTarget,
max(case when hgt    is not null then lvl end) over() as hgtTarget
  from Foobar)
select max(case lvl when colorTarget  then color  end) as color,
       max(case lvl when lengthTarget then length end) as length,
       max(case lvl when widthTarget  then width  end) as width,
       max(case lvl when hgtTarget    then hgt    end) as hgt
  from tmp;

まず、Window関数のmax関数で、colorやlengthなどが非nullであるlvlの最大値を求めてます。
次に、集約関数のmax関数で、そのlvlの行の、colorやlengthなどの値を求めてます。

SQLのイメージは下記となります。
4つの、Window関数のmax関数にそれぞれ対応した、黄緑線と青線と緑線と紫線を引いてます。



パズル56. ホテルの部屋番号

Hotelテーブル
floor_nbr  room_nbr
---------  --------
        1      null
        1      null
        1      null
        2      null
        2      null
        3      null

room_nbrを(floor_nbr*100) + 1から始まる連番 に更新する。

更新結果
floor_nbr  room_nbr
---------  --------
        1       101
        1       102
        1       103
        2       201
        2       202
        3       301

-- ■■■ データ作成スクリプト ■■■
create table Hotel(
floor_nbr int,
room_nbr  int);

insert into Hotel values(1,null),
                        (1,null),
                        (1,null),
                        (2,null),
                        (2,null),
                        (3,null);

-- ■■■ 解1 インラインビューと結合させる方法 ■■■
alter table Hotel add column ColPK int auto_increment primary key;

with tmp as(
select ColPK,
floor_nbr * 100 + Row_Number() over(partition by floor_nbr) as NewVal
  from Hotel)
update Hotel a Join tmp Using(ColPK)
   set a.room_nbr = tmp.NewVal;

Window関数を使用したビューに対してUpdate文を実行すると、
MySQL 8.0では文法エラーになりますので、
Window関数を使用したインラインビューと結合させてます。

主キーがないので、オートインクリメントな主キーを追加してます。

SQLのイメージは下記となります。
Row_Number() over(partition by floor_nbr)に対応する赤線と黄緑線を引いてます。



参考リンク MySQL Bugs: #88397: CTE with window function don't work with update/delete as expected.


パズル59. 期間を結合する

TimeSheetsテーブル
TaskID  StartDate   EndDate
------  ----------  ----------
     1  2019-01-01  2019-01-03
     2  2019-01-02  2019-01-04
     3  2019-01-04  2019-01-05
     4  2019-01-06  2019-01-09
     5  2019-01-09  2019-01-09
     6  2019-01-09  2019-01-09
     7  2019-01-12  2019-01-15
     8  2019-01-13  2019-01-14
     9  2019-01-14  2019-01-14
    10  2019-01-17  2019-01-17
    11  2019-02-01  2019-02-05
    12  2019-02-03  2019-02-28
    13  2019-02-07  2019-02-11
    14  2019-03-01  2019-03-31
    15  2019-03-01  2019-03-15

TimeSheetsテーブルの重複した期間をまとめて、期間の開始と終了を表示する。

出力結果
StartDate   EndDate
----------  ----------
2019-01-01  2019-01-05
2019-01-06  2019-01-09
2019-01-12  2019-01-15
2019-01-17  2019-01-17
2019-02-01  2019-02-28
2019-03-01  2019-03-31

-- ■■■ データ作成スクリプト ■■■
create table TimeSheets(
TaskID    int primary key,
StartDate date,
EndDate   date);

insert into TimeSheets
values( 1,date '2019-01-01',date '2019-01-03'),
      ( 2,date '2019-01-02',date '2019-01-04'),
      ( 3,date '2019-01-04',date '2019-01-05'),
      ( 4,date '2019-01-06',date '2019-01-09'),
      ( 5,date '2019-01-09',date '2019-01-09'),
      ( 6,date '2019-01-09',date '2019-01-09'),
      ( 7,date '2019-01-12',date '2019-01-15'),
      ( 8,date '2019-01-13',date '2019-01-14'),
      ( 9,date '2019-01-14',date '2019-01-14'),
      (10,date '2019-01-17',date '2019-01-17'),
      (11,date '2019-02-01',date '2019-02-05'),
      (12,date '2019-02-03',date '2019-02-28'),
      (13,date '2019-02-07',date '2019-02-11'),
      (14,date '2019-03-01',date '2019-03-31'),
      (15,date '2019-03-01',date '2019-03-15');

-- ■■■ 解1 下界との連結有無を求め、累計でグループ化する方法 ■■■
with tmp1 as(
select StartDate,max(EndDate) as EndDate,
case when StartDate
       <= max(max(EndDate)) over(order by StartDate
                                 Rows between Unbounded Preceding
                                          and 1 Preceding)
     then 0 else 1 end as WillSum
  from TimeSheets
group by StartDate),
tmp2 as(
select StartDate,EndDate,
sum(WillSum) over(order by StartDate) as GID
  from tmp1)
select min(StartDate) as StartDate,max(EndDate) as EndDate
  from tmp2
group by GID
order by GID;

最初に、StartDateの重複排除で、group by StartDateとし、EndDateは、max(EndDate)とします。

期間の連結について、考察してみます。行Xの期間が他の行Yの期間と連結するのは、下記の2つの場合が考えられます。

場合1 X.StartDate < Y.StartDate かつ Y.StartDate <= X.EndDate
場合2 X.StartDate > Y.StartDate かつ X.StartDate >= Y.EndDate

場合1と場合2の数式で、行Aから見て他の行Bと連結しているなら、行Bから見ても行Aと連結していると分かります。

以上により、StartDateの昇順に行を見ていって、
StartDateが自分未満の行(下界の行)と連結してたら0、連結してなかったら1とする数の累計で、
グループ化すればいいと分かります。

SQLのイメージ(第1段階)は下記となります。
group by StartDateで赤線を引いてます。



SQLのイメージ(第2段階)は下記となります。
max(max(EndDate)) over(order by StartDate Rows between Unbounded Preceding and 1 Preceding) 
に対応する黄緑線を引いてます。



SQLのイメージ(第3段階)は下記となります。
sum(WillSum) over(order by StartDate) に対応する黄緑線と、group by GID に対応する赤線を引いてます。



パズル62. レポートの整形

Namesテーブル
name
-------
Al
Ben
Charlie
David
Ed
Frank
Greg
Howard
Ida
Joe
Ken
Larry
Mike
Neal

Namesテーブルのname列を、3行ごとに、1行で表示する(最終行の余った列は、nullとする)。
出力順は、行も列も、Namesテーブルのname列の昇順とする。

出力結果
name1  name2   name3
-----  ------  -------
Al     Ben     Charlie
David  Ed      Frank
Greg   Howard  Ida
Joe    Ken     Larry
Mike   Neal    null

-- ■■■ データ作成スクリプト ■■■
create table Names(name char(7) primary key);

insert into Names values('Al'),
                        ('Ben'),
                        ('Charlie'),
                        ('David'),
                        ('Ed'),
                        ('Frank'),
                        ('Greg'),
                        ('Howard'),
                        ('Ida'),
                        ('Joe'),
                        ('Ken'),
                        ('Larry'),
                        ('Mike'),
                        ('Neal');

-- ■■■ 解1 商と余りで分類する方法 ■■■
with tmp as(
select name,
-1 + Row_Number() over(order by name) as rn
  from Names)
select
max(case rn%3 when 0 then name end) as name1,
max(case rn%3 when 1 then name end) as name2,
max(case rn%3 when 2 then name end) as name3
  from tmp
group by truncate(rn/3 , 0)
order by truncate(rn/3 , 0);

0以上の整数を3で割った時の、商と余りは下記となります。

0 割る 3 = 0 余り 0
1 割る 3 = 0 余り 1
2 割る 3 = 0 余り 2
3 割る 3 = 1 余り 0
4 割る 3 = 1 余り 1
5 割る 3 = 1 余り 2
6 割る 3 = 2 余り 0
7 割る 3 = 2 余り 1
8 割る 3 = 2 余り 2
省略

以上により、商でグループ化しつつ、
余りに対応したname列を、max関数とcase式を組み合わせて表示すればいいと分かります。

SQLのイメージは下記となります。group by truncate(rn/3 , 0) で赤線を引いてます。



パズル63. 連続的なグルーピング

Tテーブル
num  data
---  ----
  1  aaaa
  2  aaaa
  3  bbbb
  6  bbbb
  8  aaaa
 10  cccc
 12  bbbb
 15  bbbb

各data値がnumの何番から何番まで連続しているかを、その出現順でまとめる。

出力結果
Low  High  data
---  ----  ----
  1     2  aaaa
  3     6  bbbb
  8     8  aaaa
 10    10  cccc
 12    15  bbbb

-- ■■■ データ作成スクリプト ■■■
create table T(
num  int primary key,
data char(4));

insert into T values( 1,'aaaa'),
                    ( 2,'aaaa'),
                    ( 3,'bbbb'),
                    ( 6,'bbbb'),
                    ( 8,'aaaa'),
                    (10,'cccc'),
                    (12,'bbbb'),
                    (15,'bbbb');

-- ■■■ 解1 Lag関数でシーケンス開始を検知する方法 ■■■
with tmp1 as(
select num,data,
case when data = Lag(data) over(order by num)
     then 0 else 1 end as WillSum
  from T),
tmp2 as(
select num,data,
sum(WillSum) over(order by num) as GID
  from tmp1)
select min(num) as low,max(num) as high,data
  from tmp2
group by GID,data
order by GID;

numの昇順で行を見ていって、
Lag関数を使って、シーケンス開始なら1、シーケンス開始でなければ0とする数を求め、
その累計でグループ化してます。

SQLのイメージは下記となります。
sum(WillSum) over(order by num) as GIDに対応する黄緑線と、
group by GID,dataに対応する赤線を引いてます。



-- ■■■ 解2 旅人算メソッドを使う方法 ■■■
with tmp as(
select num,data,
 Row_Number() over(order by num)
-Row_Number() over(partition by data order by num) as distance
  from T)
select min(num) as low,max(num) as high,data
  from tmp
group by data,distance
order by min(num);

中学受験の算数で有名な旅人算の感覚を使う方法もあります。

1進む条件が異なる4人の旅人(旅人X,旅人A,旅人B,旅人C)が、
数直線の原点からプラス方向に同時にスタートしたとして、下記のように考えてます。

・必ず1進む旅人Xの位置 = Row_Number() over(order by num)
・dataがaaaaなら1進む旅人Aの位置 = Row_Number() over(partition by data order by num)
・dataがbbbbなら1進む旅人Bの位置 = Row_Number() over(partition by data order by num)
・dataがccccなら1進む旅人Cの位置 = Row_Number() over(partition by data order by num)

そして、group by data,distance によって、
dataに対応した旅人の種類(旅人A,旅人B,旅人Cのどれか)と、旅人Xとの距離でグループ化してます。

SQLのイメージは下記となります。
Row_Number() over(order by num)に対応する1人の旅人と、
Row_Number() over(partition by data order by num)に対応する3人の旅人をイメージし、
group by data,distanceに対応する赤線を引いてます。



パズル66. 数独パズル

ニコリの数独の問題を解きます。

問題


答え


-- ■■■ 数独を解くのに使う文字列関数 ■■■
select
InStr(Val,'a') as tes1,
InStr(Val,'X') as tes2,
SubStr(Val,1,2) as tes3,
SubStr(Val,3,2) as tes4,
Insert(Val,2,3,'XYZ') as tes5,
Insert(Val,2,0,'XYZ') as tes6
  from (select 'abcdef' as Val) tmp;

出力結果
+------+------+------+------+--------+-----------+
| tes1 | tes2 | tes3 | tes4 | tes5   | tes6      |
+------+------+------+------+--------+-----------+
|    1 |    0 | ab   | cd   | aXYZef | aXYZbcdef |
+------+------+------+------+--------+-----------+

数独を解くのに使う文字列関数の紹介です。

-- ■■■ 解1 幅優先探索を使う方法 ■■■
with recursive Question(Val) as(
select concat('800005100',
              '001000800',
              '040200090',
              '000030002',
              '123406789',
              '600010000',
              '080009050',
              '002000400',
              '007600001')),
Renban1To9(Cnt) as(
select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union
select 7 union select 8 union select 9),
rec(LV,Val) as(
select 1,Val from Question
union all
select LV+1,
case when SubStr(Val,LV,1) = '0'
     then Insert(Val,LV,1,Cnt)
     else Val end
  from rec a Join Renban1To9 b
    on a.LV <= 81
   and (SubStr(a.Val,a.LV,1) = '0' or b.Cnt=1)
 where SubStr(Val,LV,1) != '0'
    or (InStr(SubStr(Val,truncate((LV-1)/9,0)*9+1,9),Cnt) = 0 -- 横チェック
    and InStr(SubStr(Val,(LV-1)%9+1   ,1),Cnt) = 0 -- 縦チェック
    and InStr(SubStr(Val,(LV-1)%9+1+ 9,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+18,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+27,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+36,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+45,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+54,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+63,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+72,1),Cnt) = 0
    -- 正方形チェック
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)   ,3),Cnt)=0
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+ 9,3),Cnt)=0
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+18,3),Cnt)=0))
select SubStr(a.Val,1+9*(b.Cnt-1),9) as Answer
  from rec a,Renban1To9 b
 where a.LV=82
order by b.Cnt;

出力結果
+-----------+
| Answer    |
+-----------+
| 839765124 |
| 261394875 |
| 745281396 |
| 594837612 |
| 123456789 |
| 678912543 |
| 386149257 |
| 912573468 |
| 457628931 |
+-----------+

再帰項で、1から9までの連番テーブルとの内部結合を繰り返しつつ、
where句で、その連番でマスを埋めることが可能かをチェックしてます。

SQLのイメージ(探索木の深さは3まで)は下記となります。
幅優先探索の探索木をイメージしてます。
未確定マスは、1から9までのノードが作成候補になりつつ、枝切りされていくイメージです。

高さ1のノードが、問題の1番上の行の1番左のマスに対応し、
高さ2のノードが、問題の1番上の行の左から2番目のマスに対応し、
高さ3のノードが、問題の1番上の行の左から3番目のマスに対応します。



参考資料

『SQLパズル 第2版』サポートページ