skillup

技術ブログ

Database

EXISTSについて

投稿日:

今回はEXISTSについてです。

実務では伝票と明細との検索関連の処理で結構出てきます。

なお、達人に学ぶ~では論理学について少しふれており、この領域を本気で理解する場合は論理学を勉強する必要があります。

下記のようなテーブル(meetings)があり、欠席者を求めるとします。

meeting | person
————————————-+————————————
第1回 | 伊藤
第1回 | 水島
第1回 | 坂東
第2回 | 伊藤
第2回 | 宮田
第3回 | 坂東
第3回 | 水島
第3回 | 宮田

求めたいレコードは下記のようなものです。

meeting | person
————————————-+————————————
第1回 | 宮田
第2回 | 坂東
第2回 | 水島
第3回 | 伊藤

ここに出ているメンバーで全員と考えていいので、まずはメンバー全員のリストを求めればいいのでは・・・と思ったのですが、それだけやっても求められないと気づきました。

基本的な考え方ですが、皆勤だった場合のテーブルを考えます。

皆勤テーブルは下記のように求められます。

自己結合ですね。この中で回数とpersonでの重複を除き、全員出そうとおもうと、上記のようにDISTINCTをとります。実際にSELECTで全列を出力してみて、重複を取り除いて、全員が出席している場合を抽出しようとすると必然的にこのようになります。

EXISTSうんぬんよりはここがちょっとむずいかもです。

考え方としては皆勤テーブルをベースに考え、皆勤テーブルにあってmembersの中にないレコードを探せればOKです。そのSQLですが、下記のようになります。

ただしもっと簡単な方法がありまして、前回やった集合の考えを使えば差分を取れば一発です。

否定と肯定の言い換え

EXISTSを使ってSQLで条件を検索する場合、肯定の表現を否定の表現を入れ替えることが多いです。

例えば下記のようなテーブル(testscores)があると仮定して、全ての点数が50点以上の生徒を求めてみましょう。

student_id | subject | score
————+———+——-
100 | 算数 | 100
100 | 国語 | 80
100 | 理科 | 80
200 | 算数 | 80
200 | 国語 | 95
300 | 算数 | 40
300 | 国語 | 90
300 | 社会 | 55
400 | 算数 | 80

この場合の条件の作り方ですが、全てが50点以上というよりも50点未満の点数を持っていないという条件分岐のほうが簡単です。

50点以上の点数を保持しているだと、一教科でも50点以上ならカウントされてしまうためEXISTSでは取得できません。

この場合、NOT EXISTSを使って簡単に作れます。

結果として下記のようなSQLを作ればOKです。

今度は若干複雑になりまして、教科が算数ならば80点以上であり、国語ならば50点以上の生徒を求めてみます。

まず下記のように算数と国語で基準点に達した生徒にフラグを立てるようにし、これで求めようとおもいました。

これでいけるかと思ったんですがWHERE句の中でmathやjapaneseが使えないことが発覚。

この場合は下記のように書くようです。

これもだめでした。これだと算数&80で国語&50点というのが1行で成り立っていないとダメなようです。
下記のように直します
※こういった複雑なSQLの場合はEXISTS以下だけでチェックしてみたりするのが良いです。

これで行けるかと思いましたが、やはりこれだと算数、国語どっちかの条件に合致した場合出てきてしまうので否定で考えます。

student_id | subject | score
————+———+——-
100 | 算数 | 100
100 | 国語 | 80
100 | 理科 | 80
200 | 算数 | 80
200 | 国語 | 95
400 | 算数 | 80
こうなりました。

さらに条件を絞り、subjectが国語と算数のみでstudent_idでグルーピングし、行数が2つ(国語と算数両方必要なため)あるものを絞り込みます。

student_id | count
————+——-
100 | 2
200 | 2

これでOKです。長かった・・・

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

SQLの高速化について&explain

本日はSQLの高速化について。 高速化といってもさまざまなテクがあると思うのですが、代表的な考え方に関して。 Contents1 高速化に関して1.1 index1.2 ディスクアクセスを減らす1.3 …

no image

MYSQL group_concat,cast等

実務でいろいろとトラブルがあり、データベースから在庫データを見てくれ~なんて依頼がありましたんでSQLをごりごり書いていたんですが、普段使わないようなSQLの構文を使ったのでここでちょっと紹介します。 …

no image

データクレンジング

リレーショナルデータベースでデータを管理する前に、しなくてはいけないことはデータをデータベースに登録できる形に整形することです。 このことをデータクレンジングといいます。 これを行わずに何も考えずにデ …

no image

MySQLのセキュアな設定

以前SSHの設定についていろいろ書いたんで今回はMySQLに関して。 Contents1 基本的な処方箋(MySQLに限らないかも)2 ホストのアクセスを制限する3 LOCAL INFILEコマンドを …

no image

正規化について&EXTRACT

Contents1 正規化とは?2 第一正規化3 SQLネタ EXTRACT 正規化とは? 正確な定義は難しいですが、8割ぐらいあっている定義としては「適切なテーブルに分割すること」です。※ただし正確 …