全称量化は「全ての行について〜」となっている表現のことで、これをSQLで実現する場合は「〜でない行が1つもない」という2重否定に変換する。
まずは、以下のようなテーブルについて考える。
| id | title | score |
|---|---|---|
| 1 | A | 100 |
| 1 | B | 80 |
| 1 | C | 80 |
| 2 | A | 80 |
| 2 | B | 95 |
| 3 | A | 40 |
| 3 | B | 90 |
| 3 | D | 55 |
| 4 | A | 80 |
各idを割り振られた批評家たちが、該当のタイトルの映画に対して評価点として何点をつけたかがまとめられている。
この中で、「評価した全ての映画に対して50点以上をつけた批評家」を見つけ出したい場合は、上記の2重否定への変換を行うと「50点以下と評価した映画が1つもない批評家」になる。
これをSQLで表現すると以下のようになる。
SELECT DISTINCT id FROM movies m1 WHERE NOT exists( SELECT * FROM movies m2 WHERE m1.id = m2.id AND m2.score < 50 );
| id |
|---|
| 1 |
| 2 |
| 4 |
また、例えば「映画Aに80点以上、映画Bに50点以上をつけた批評家」を抜き出す場合は、サブクエリ内でcase式を使って実現することができる。
select distinct id from movies m1 where not exists( select * from movies m2 where m1.id = m2.id and 1 = case when title = 'A' and score < 80 then 1 when title = 'B' and score < 50 then 1 else 0 end );
データの都合で同じ結果が出るが、これで正しい。
ポイントは初めにも書いたように、「全ての行について〜」というのを「〜な行は1つもない」と書き換えてクエリを作るところ。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
posted with amazlet at 17.07.10
