skillup

技術ブログ

Database

HAVING句の活用 発展編

投稿日:

今回からはまたまたHAVING句です。

下記のようなテーブル(teams)があり、全員が待機状態のチーム(全員がそろっている)を求めます。

member | team_id | status
——————+———+————-
ジョー | 1 | 待機
ケン | 1 | 出動中
ミック | 1 | 待機
カレン | 2 | 出動中
キース | 2 | 休暇
ジャン | 3 | 待機
ハート | 3 | 待機
ディック | 3 | 待機
ベス | 4 | 待機
アレン | 5 | 出動中
ロバート | 5 | 休暇
ケーガン | 5 | 待機

回答は下記のようになります。

team_id
———
4
3

これをNOT EXISTSとHAVINGを使って2パターンで記述する問題なのです。

HAVING

NOT EXISTS

本にも書いてありましたが、私もHAVINGのほうがわかりやすいですね。

CASEとHAINGは見た目がごつい割には理解しやすくフラグ的な扱いができるのでプログラマには向いている気がします。

ちなみにHAVINGを使って下記のように書く回答もありました。

一瞬わからなかったのですが、これはもしteam_id単位で複数のstatusが存在していたら最大と最小が違うという意味なんですね。分かりにくい場合はstatusを数字で考えるとよいと思います。

次は下記のテーブルで拠点ごとに資材のダブりをみるSQLです。

center | receive_date | material
—————–+————–+——————–
東京 | 2007-04-01 | 錫
東京 | 2007-04-12 | 亜鉛
東京 | 2007-05-17 | アルミニウム
東京 | 2007-05-20 | 亜鉛
大阪 | 2007-04-20 | 銅
大阪 | 2007-04-22 | ニッケル
大阪 | 2007-04-29 | 鉛
名古屋 | 2007-03-15 | チタン
名古屋 | 2007-04-01 | 炭素鋼
名古屋 | 2007-04-24 | 炭素鋼
名古屋 | 2007-05-02 | マグネシウム
名古屋 | 2007-05-10 | チタン
福岡 | 2007-05-10 | 亜鉛
福岡 | 2007-05-28 | 錫

下記のレコードが出力されればOKです。

center
—————–
東京
名古屋

これまた回答を見るとなんの変哲もないですが、意外にロジックが考えにくい題材だと思います。

要はダブりがなければ全体の列数とdistinctなmaterialの列数が一緒になるんですね。なのでそうなっていないケースを求めます。

ちなみに下記の書いてあげると全てのcenterのstatusがわかり大変みやすいです。

center | status
—————–+————
大阪 | ダブりなし
東京 | ダブりあり
福岡 | ダブりなし
名古屋 | ダブりあり

ちなみにEXISTSでもいけます。こちらだとダブっている素材もわかりますね。

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

エンティティの抽出と主キー決定

主に設計に関することのメモ。 Contents1 業務フロー分析2 エンティティの抽出3 エンティティの関連付け4 主キーの抽出を行う4.1 主キーの特徴4.2 サロゲートキーのメリット4.3 サロゲ …

no image

論理設計のアンチパターン その2

今回は論理設計のアンチパターンの続きです。 今までに比べると何も意図がないというものではなく、パフォーマンスを考えて設計されているようなものが多いです。 ただし、中には絶対に許されないタイプのものもあ …

no image

データベースのインデックスについて

今回はデータベースのインデックスに関して。 検索条件を早くする場合、何よりも速度が速くなるのはインデックスを張ることでしょう。 インデックスを張ることは単語を索引順に並べることですので、劇的に速度が向 …

no image

日付がらみの処理に関して(MySQL&Java)

MySQL触りだして3年ぐらいたつんですがいまだに整理できないことが多いです。(特に日付がらみ) ちょっとJavaのネタと合わせて整理しておこうかなーと思います。 Contents1 MySQLの日付 …

no image

テストのダミーデータ作成

データベースに大量のデータを作りたいときにいつもあああやhoge,aaaですとデータという感じがしないですし、抽出や集計ができません。 なるべく自然に近いデータが欲しいのですが、簡単に作れる方法があり …