skillup

技術ブログ

Database

自己結合のイメージ

投稿日:2016年11月5日 更新日:

前回に引き続き結合について考えます。

例えば下記のようなテーブルがあり、重複行を削除するとします。

1 りんご 50
2 みかん 100
3 みかん 100
4 みかん 100
5 バナナ 80

この場合、

下記のようなSQLを書けば削除できます。テーブルに関しては前エントリーを参照。

これはP1とP2を集合とみるとnameとpriceが同一なレコードがある場合、りんごとばななではともにSELECT句の中のidが1になり、条件が不等号でイコールを含まないので何も起こりません。

逆にみかんに関しては34が条件に合致するため削除されます。2つの図を紙で書いて組み合わせを考えるとわかりやすいかも・・

部分的に不一致なキーの検索

http://mickindex.sakura.ne.jp/database/db_support_sinan.html

この例にあるような部分的に不一致なキー検索ですが、family_idが一緒でなおかつ住所が違うものですので、下記のように書けます。

 ランキング

またさきほどのリンクページにあるような検索についても解説します。

Postgresなら下記のような分析計の関数(RANKなど)が使えるので非常に楽です。

ただしMySQLでは使えませんので、これをサブクエリを使って書くことになります。

自己結合のイメージ

「達人に学ぶSQL徹底指南書」には上記の結合を集合の考えを使って非常にわかりやすく書いてあります。

集合 自分(p1)の金額 自分より高い値段(p2) 自分より高い値段の個数
s0 100 0 0
s1 80 100 1
s2 50 100,80 2
s3 30 100,80,50 3

なお集合の考え方でいくとs3が最大でs0が最小となり同心円上の形になっているのがわかるかと思います。

結合でかくとさらにこれは下記のように書けます。

なお、わかりやすくするため金額の重複をはずし、果物の種類を減らします。具体的にはhttp://mickindex.sakura.ne.jp/database/db_support_sinan.htmlにすべてのっています。

結果が下記のようになります。

name | name
——–+——–
みかん |
ぶどう | みかん
ぶどう | スイカ
スイカ | みかん
レモン | みかん
レモン | ぶどう
レモン | スイカ

自己結合の注意点

自己結合に関しては下記のような注意点が必要です。

  • 非等値結合(<や>などの=以外の記号)と組み合わせる
  • GROUP BYと組み合わせると再帰的集合を作ることができる
  • 本当のテーブルが2つあると考えるとわかりやすい。
  • 集合で考える(図、表を使う)
  • 処理コストが高いので結合キーにはインデックスを貼る

-Database
-

執筆者:


comment

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

関連記事

no image

サブクエリ 分析関数の代替案として

今回は分析関数系のネタです。 以前にも分析関数を少し学習しましたがMySQLにはないので、サブクエリを使い書くことになります。 下記のような入出金講座があるとします。 Accounts prc_dat …

no image

MySQLでtext型が大量にあるもののリストア 

MySQLでのリストアについて。 先日実務でtext型のカラムが複数あるテーブルを読もうとしたら下記エラーがでてこけました。

なにやら …

no image

データ構造の基礎知識 後編 木構造

データベースの学習をしていたときの復習です。 データ構造の基礎知識 前編 メモリとポインタ、配列と連結リスト データ構造の基礎知識 中編 ハッシュ 今回はもう少し複雑な「木構造」について考えてみます。 …

no image

CASE式のすすめ

SQL実践入門を7割がたぐらい終えたところで同著者の方の「達人に学ぶSQL徹底指南書」を学習しようと思っています。 この方の著書は気づいたら結構読んでましたね。 Contents1 CASE式での注意 …

no image

HAVING句の活用 発展編

今回からはまたまたHAVING句です。 下記のようなテーブル(teams)があり、全員が待機状態のチーム(全員がそろっている)を求めます。 member | team_id | status &#82 …

アーカイブ