「達人に学ぶSQL徹底指南書」（ミック著）を読んで（眺めて）「自己結合は使いこなせば非常に便利な技術です」とあった。そしてミックさんは本の中でも多用している。
自己結合ってなんか頭の中でこんがらがって難しい。
でも理解したら一段高いところに行けそう。だから理解してみようと努める。
なお、小学生レベルの脳みその持ち主の私が理解できるように分解したものを記載しています。実際の動きや原理とは異なる場合があると思いますのでご承知おきください。
シリーズ自己結合
直積とはなんぞや、から始めたのが前々回の記事。
自己結合をやってみよう、が前回の記事。
【access/SQL】自己結合を理解するように努める～①その前に、直積 とは
【access/SQL】自己結合を理解するように努める～②自己結合をやってみる
今回は自己結合の発展。
①婚活パーティーでカップルができたのでIDで紐づけする
②同じ値段の商品を抽出
③ひとつ前の値と比べる
④ランキング
の①②をやる
①婚活パーティーでカップルができたのでIDで紐づけする
ちょっと意味わからない例ですが。。。
婚活パーティー参加者名簿にカップルとして成立した女性の「夫ID」カラムに夫になる人のIDを入力する。男性の「妻ID」には妻になる人のIDを入力する。
ID, 氏名 , 性別 , 夫ID , 妻ID
1 , 山田 , 女 , 4 , NULL
2 , 佐藤 , 女 , 6 , NULL
3 , 田中 , 女 , NULL , NULL
4 , 高島 , 男 , NULL , 1
5 , 加藤 , 男 , NULL , NULL
6 , 小林 , 男 , NULL , 2
SELECT T1.ID, T1.氏名 AS 妻, T2.氏名 AS 夫
FROM 名簿 AS T1 INNER JOIN 名簿 AS T2
ON T1.夫ID = T2.ID
片方のテーブルの夫IDともう一つのテーブルのIDを紐づけました。
結果
ID, 妻 , 夫
1 , 山田 , 高島
2 , 佐藤 , 小林
1番の山田さんは4番の高島さんと、2番の佐藤さんは6番の小林さんと夫婦になりました。（早い。。。）
3番の田中さんと5番の加藤さんはこの婚活パーティーでは特にこれはと思う人がいなかったようです。ま、早まらない方がいいですよね。
②同じ値段の商品を抽出
このテーブルを使う。文房具屋さんの商品テーブル
商品名 , 単価
鉛筆 , 100
定規 , 300
ボールペン , 200
シャープペン , 300
このテーブルの中から同じ値段の商品を探して抽出します。
SELECT T1.商品名, T1.単価
FROM 商品 AS T1, 商品 AS T2
WHERE T1.単価 =T2.単価 AND T1.商品名<>T2.商品名;
単価が同じであることを抽出条件にします。
ただし、同じ商品は同じ値段であるのはあたりまえなので商品名が違うことも条件に加えます。
なお、＜＞や＞を使うことを、自己結合の非等値結合というらしい。
結果は以下です。
商品名 , 単価
定規 , 300
シャープペン , 300
途中経過を脳内整理。直積を作ってみて考える。
商品名 , 単価, 商品名2 , 単価2
鉛筆 , 100 , 鉛筆 , 100 単価は同じだが商品名が同じ✕
鉛筆 , 100 , 定規 , 300 単価が違う✕
鉛筆 , 100 , ボールペン , 200 単価が違う✕
鉛筆 , 100 , シャープペン , 300 単価が違う✕
定規 , 300 , 鉛筆 , 100 単価が違う✕
定規 , 300 , 定規 , 300 単価は同じだが商品名が同じ✕
定規 , 300 , ボールペン , 200 単価が違う✕
定規 , 300 , シャープペン , 300 単価同じ、商品名違う〇
ボールペン , 200 , 鉛筆 , 100 単価が違う✕
ボールペン , 200 , 定規 , 300 単価が違う✕
ボールペン , 200 , ボールペン , 200 単価は同じだが商品名が同じ✕
ボールペン , 200 , シャープペン , 300 単価が違う✕
シャープペン , 300 , 鉛筆 , 100 単価が違う✕
シャープペン , 300 , 定規 , 300 単価同じ、商品名違う〇
シャープペン , 300 , ボールペン , 200 単価が違う✕
シャープペン , 300 , シャープペン , 300 単価は同じだが商品名が同じ✕
で、〇の2行が抽出され、左側の商品名、単価が出力される。
③④は次回の記事で
コメント