達人SQL1-10の「各隊、隊員点呼！」のまとめです。
ここでやりたいことは、「特定の集合の中ですべての値が揃っているものを抜き出したい」というもの。
例えば、以下のようにgroup_idとそれぞれの行の状態をstatusで持っているようなテーブルを考える。
| id | group_id | status |
|---|---|---|
| 1 | 1 | ◯ |
| 2 | 1 | ◯ |
| 3 | 1 | ◯ |
| 4 | 2 | ◯ |
| 5 | 2 | ☓ |
| 6 | 3 | ◯ |
| 7 | 4 | ◯ |
| 8 | 4 | ☓ |
| 9 | 4 | ◯ |
| 10 | 4 | ☓ |
この中で、すべての行のstatusが「◯」になっているgroup_idを取得したい場合、以前も出ててきた全称文の書き換えを利用すると、
select group_id, status from Groups g1 where not exists( select * from Groups g2 where g1.group_id = g2.group_id and g2.status <> '◯' );
となる。 これは、「含まれるすべての行のstatusが◯」という条件と「含まれる行の中で、◯ではない行が１つもない」という条件で考えている。
この方法はパフォーマンスも良いし慣れてくると非常に便利だが、2重否定をクエリで表現すると読みにくくなってしまうのも確かである。
そこで、これをHAVING句を使って表現すると次のようなクエリになる。
select group_id from Groups group by group_id having count(*) = sum( case when status = '◯' then 1 else 0 end );
このクエリでは、「同じgroup_idの行の数と、そこに含まれる行のうちstatusが◯になっている行の数が一致するならば、そのgroup_idのすべての行のstatusは◯である」ということになる。
これはクエリ自体も非常にわかりやすい。
また、今度はhaving句と極値関数を組み合わせて以下のようにしても同じ結果を得ることができる。
select group_id from Groups group by group_id having max(status) = '◯' and min(status) = '◯';
このhaving句はあまり見慣れないものだが、これは「statusがの最大値と最小値が一致する時、そこには1つの値しか含まれていない」ということを表している。
これは極値関数を使っているのでインデックスを利用することができ、パフォーマンスも非常に良くなる。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
posted with amazlet at 17.07.10
