MySQL 8.0のSQLの新機能を解説

MySQL 8.0で『SQLパズル』の問題を解く

MySQL 8.0のSQLの新機能を解説第3回

山岸賢治[著]

2019/06/06 11:00

ポスト

ダウンロードサンプルファイル (3.6 KB)

　本連載では、MySQL 8.0のリリースを記念して、3回にわたってSQLの新機能を解説します。最終回となる第3回の本稿では、第1回、2回で紹介した機能を使って、書籍『SQLパズル』の問題を解きます。

ポスト

はじめに

　本稿では、第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述語で定義したビューを参照しています。

参考リンク：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で赤線を引いています。