CodeZineのMySQLの記事(シーズン2)の元原稿
第2回 再帰With句のサンプル集
第3回 MySQL8.0でSQLパズルの問題を解く
今回のテーマ
動作確認環境
MySQL 8.0.15
目次
パズル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番目のマスに対応します。
参考資料