はじめに
本稿では、第1、2回で得た知識をより深めるために、MySQL 8.0で、『SQLパズル 第2版』の問題を解きます。
対象読者
- MySQLのSQLの理解を深めたい方
必要な環境
本稿で扱うSQLは、MySQL 8.0.15で動作確認しました。
パズル17. 人材紹介会社
以下の問題を解きます。なお、問題は『SQLパズル 第2版』76、77ページより引用、簡潔に改変したものです。
人材紹介会社のデータベースから「特定のスキルの条件を満たす登録者を見つけ出す」ためのSelect文はどのようになるでしょうか。テーブルデータは以下の通りです。
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(16), primary key(ID,Skill)); insert into Skills values(100,'会計'), (100,'在庫管理'), (100,'製造'), (200,'会計'), (200,'在庫管理'), (300,'製造'), (400,'在庫管理'), (400,'製造'), (500,'会計'), (500,'製造');
解として、以下の2つの方法が考えられます。
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;
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. ダイレクトメール
以下の問題を解きます。なお、問題は『SQLパズル 第2版』81ページより引用、簡潔に改変したものです。
ダイレクトメールの顧客リストから、特定の人の行を削除するDelete文を考えます。
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);
解として、以下の2つの方法が考えられます。
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述語で定義したビューを参照しています。
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で赤線を引いています。