SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

MySQL 8.0のSQLの新機能を解説

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

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 本稿では、第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で赤線を引いています。

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

次のページ
パズル21. 飛行機と飛行士

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
MySQL 8.0のSQLの新機能を解説連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2680 2019/06/06 11:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング