本日はHAVINGについて。
かろうじて用法はしっており、たまに使うこともありますが、あまりしっかり理解しているとはいえない状況ですので、掘り下げてみようと思います。
WHEREとは違い、抽出した結果に対しての絞り込みを行います。GROUP BYで絞ったあとの抽出などに最適です。
下記のようなテーブル(SeqTbl)があるとします。
seq | name
—–+———-
1 | ディック
2 | アン
3 | ライル
5 | カー
6 | マリー
8 | ベン
番号が歯抜けになっているのがわかるかと思います。歯抜けがあるかどうかは
1 |
SELECT '歯抜けあり' AS hanuke FROM SeqTbl HAVING COUNT(*) <> MAX(seq); |
このSQLで出せます。(歯抜けがなければCOUN(*)すべての列数とMAX(seq)が一緒になります。)
また歯抜けになっているseqを出したい場合は、下記のようなSQLになります。
1 2 3 4 5 6 7 8 9 |
SELECT --条件に合致したSQLを表示する seq+1 AS hanuke FROM SeqTbl WHERE --seqより+1でseqの中にないものを選ぶ (seqにしてしまうと当然0行になる) --下記SELECT文の中にはseqのリストが詰まっている状態 seq+1 NOT IN ( SELECT seq FROM SeqTbl) ; |
下記のようなテーブル( graduates)があり最頻値(このケースでいうと20000と10000を求めるとします)
name | income
————+——–
サンプソン | 400000
マイク | 30000
ホワイト | 20000
アーノルド | 20000
スミス | 20000
ロレンス | 15000
ハドソン | 15000
ケント | 10000
ベッカー | 10000
スコット | 10000
考える場合のステップですが、下記のような流れになるかと思います。
- まず回数のみのテーブルを考慮
- そのテーブルをもとに最大値の算出
- その最大値と同様のデータを抽出
1の段階で下記テーブルを作ります。
1 2 3 4 5 6 |
SELECT count(*) AS freq0 FROM graduates GROUP BY income |
2の段階(1のテーブルをベースに考えます)
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT MAX( freq0 ) FROM ( SELECT count(*) AS freq0 FROM graduates GROUP BY income ) AS freq_tb |
3の段階(これが最終解になります。)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT income, count(*) AS freq FROM graduates GROUP BY income HAVING count(*) = ( SELECT MAX( freq0 ) FROM ( SELECT count(*) AS freq0 FROM graduates GROUP BY income ) AS freq_tb ) |
またALLを使った方法でも可能です。
こっちのほうが、わざわざ最大値を絞らなくていいので楽ですね。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT income, count(*) AS freq FROM graduates GROUP BY income --↓これですべてのCOUNT以上のCOUNTを対象にするので --必然的に最大値がとれます。 HAVING count(*) >= ALL ( SELECT count(*) AS freq0 FROM graduates GROUP BY income ) |