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

cakePHPでの直SQL

今回はCakePHPにて直のSQLを書く方法を。 cakePHPにて大概の処理はもともと備わっているコマンドでなんとかなりますが、まれに直SQLを書いたほうがらくなこともあります。 書き方その1 [c …

no image

cakeでのトランザクション、コミット、ロールバック

cakePHP(2.X系)でのトランザクション、コミット、ロールバックについて。 cakePHPでトランザクションを書ける場合、Model内に [crayon-5c48fe7a7ae711114801 …

no image

SQL case式やウィンドウ関数の威力

プログラマには「プログラムは思った通りに動かない。書いた通りに動く。」「バグではない仕様です」なんて面白い格言がいろいろありますが、データベースの世界にも「WHERE句で条件分岐させるのは素人のやるこ …

no image

MySQLのLIMIT,OFFSETに関して&explainの見方など

自作のWEBアプリを作っていたところSELECT句が異常に遅いケースがありました。 発見までにかなり時間がかかったんですが、不可思議な現象としてはOFFSETが小さいときと大きいときで検索スピードが全 …

no image

SQLクエリ比較

クエリの比較 SQLにおいては全く同じ結果を返すのであってもその検索結果が異なるということはよくあります。 例えば下記のようなテーブルがあった場合 co_cd | district —&# …