HAVING句のNULLがあった時の挙動にたいしてメモリます。
COUNTの処理
対象上の列数を数えるのにCOUNTを使うと思いますが、COUNT(*)とCOUNT(列名)では動きが若干違っており、前者はそのままですが、後者はNULLを含みません。それを前提として下記のような問題を解いてみましょう。
あ、すべてサンプルデータなどはここにのっています。
stundentsテーブル
student_id | dpt | sbmt_date
————+———-+————
100 | 理学部 | 2005-10-10
101 | 理学部 | 2005-09-22
102 | 文学部 |
103 | 文学部 | 2005-09-10
200 | 文学部 | 2005-09-22
201 | 工学部 |
202 | 経済学部 | 2005-09-25
上記は課題提出のテーブルだとします。ここから全ての生徒が課題を提出している。つまりはsbmt_dateがNULLでない学部を求めるとします。(この場合ですと、理学部と経済学部になります。)
その場合COUNTとHAVUNGを使うと下記のように書けます。
|
1 2 3 4 5 6 7 8 |
SELECT dpt, COUNT(*) as num, COUNT(sbmt_date) as submit_num FROM students GROUP BY dpt |
これで下記のようなテーブルになります。
dpt | count | count
———-+——-+——-
経済学部 | 1 | 1
工学部 | 1 | 0
理学部 | 2 | 2
文学部 | 3 | 2
ここからHAVINGでフィルタリングしてみましょう。(HAVINGはエイリアスは使えないので注意です。)
|
1 2 3 4 5 6 7 8 9 |
SELECT dpt, COUNT(*) as num, COUNT(sbmt_date) as submit_num FROM students GROUP BY dpt HAVING( COUNT(*) = COUNT(sbmt_date) ) |
dpt | num | submit_num
———-+—–+————
経済学部 | 1 | 1
理学部 | 2 | 2
模範解答ではCASEを使って下記のようにも書いていました。
|
1 2 3 4 5 6 7 |
SELECT dpt FROM students GROUP BY dpt HAVING( COUNT(*) = SUM ( CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END ) ) |
これは思いつかなかったなあ・・
次はもう少し実践的です。
下記のような商品テーブル(items)と店舗テーブル(shopitems)があるとします。
item
———-
ビール
紙オムツ
自転車
shop | item
——+———-
仙台 | ビール
仙台 | 紙オムツ
仙台 | 自転車
仙台 | カーテン
東京 | ビール
東京 | 紙オムツ
東京 | 自転車
大阪 | テレビ
大阪 | 紙オムツ
大阪 | 自転車
このなかでitemsの中の商品をすべてそろえている店舗(仙台、東京)を抽出します。
まずは下記SQLを使うとitemsの中に含まれているshopitemsのレコードをすべて求めてくれます。
|
1 2 3 4 5 6 7 |
SELECT shopitems.shop, shopitems.item, FROM shopitems,items WHERE shopitems.item = items.item |
shop | item
——+———-
仙台 | ビール
仙台 | 紙オムツ
仙台 | 自転車
東京 | ビール
東京 | 紙オムツ
東京 | 自転車
大阪 | 紙オムツ
大阪 | 自転車
上記のテーブルはすべてitemsの中にあるものだけで構成されています。
これを店舗名でGROUP BYし、itemsのレコード数と一緒になれば全部含まれていることになります。
|
1 2 3 4 5 6 7 8 9 |
SELECT shop FROM shopitems,items WHERE shopitems.item = items.item GROUP BY shopitems.shop HAVING ( COUNT(*) = ( SELECT COUNT(*) FROM items ) ) |
やはり集合の考え方が大事になってきますね。いままであまり考えなかったので、図を書きながら整理するといいかもと思っています。
