Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2019/06/06 11:00

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

解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で赤線を引いています。


  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:MySQL 8.0のSQLの新機能を解説
All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5