はじめに
本稿では、第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つの方法が考えられます。
解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. ダイレクトメール
以下の問題を解きます。なお、問題は『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つの方法が考えられます。
解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述語で定義したビューを参照しています。
解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で赤線を引いています。
