
クロス結合ってどんな結合?
通常の「ON」での結合条件を使用しない結合。
結合する両テーブル、すべての組み合わせパターンのレコードを取得します。
なので、結果で得られるレコード数は [ 左のテーブルのレコード数 ] x [ 右のテーブルのレコード数 ] になります。
例えば、以下のようなテーブルA、Bがあった場合
テーブルA:
名前 |
---|
山田さん |
鈴木さん |
田中さん |
テーブルB:
クラス |
---|
りんご |
バナナ |
みかん |
// AとBをクロス結合する
SELECT *
FROM Aテーブル
CROSS JOIN Bテーブル
結合結果:3レコード(Aテーブル) × 3レコード(Bテーブル)
名前 | クラス |
---|---|
山田さん | りんご |
山田さん | バナナ |
山田さん | みかん |
鈴木さん | りんご |
鈴木さん | バナナ |
鈴木さん | みかん |
田中さん | りんご |
田中さん | バナナ |
田中さん | みかん |
のようなテーブルができます。
この例では、あまり実用性が感じられませんので、 実例を用いて説明してみます。
以下のようなテーブルがあったとします。
工事テーブル:
No | 工事名 | 金額 | 年度 |
---|---|---|---|
1 | 工事A | 100円 | 2018 |
2 | 工事B | 200円 | 2018 |
3 | 工事C | 300円 | 2018 |
支払テーブル:
工事名 | 金額 | 年度 |
---|---|---|
工事A | 100円 | 2018 |
工事A | 500円 | 2017 |
工事B | 200円 | 2018 |
工事B | 500円 | 2017 |
工事C | 300円 | 2018 |
工事C | 100円 | 2017 |
工事テーブルに存在する各工事の支払金額を、支払テーブルに存在する全年度分集計したいとき 工事テーブルを軸に支払テーブルを結合し、各工事毎に集計します。
テーブル結合条件を「工事名」にleftjoinした場合、以下のようになり左のレコードが重複してしまいます。
No | 工事名 | 金額 | 年度 | 工事名 | 金額 | 年度 |
---|---|---|---|---|---|---|
1 | 工事A | 100円 | 2018 | 工事A | 100円 | 2018 |
1 | 工事A | 100円 | 2018 | 工事A | 500円 | 2017 |
工事テーブルA.金額を集計すると、重複して計上されてしまいます。
(実際はテーブルB.金額を合計すれば良いですが、ここではあえてクロス結合で)
そこで、2つのテーブルをクロス結合すると以下のような表になります。
No | 工事名 | 金額 | 年度 | 工事名 | 金額 | 年度 |
---|---|---|---|---|---|---|
1 | 工事A | 100円 | 2018 | 工事A | 100円 | 2018 |
1 | 工事A | 100円 | 2018 | 工事A | 500円 | 2017 |
1 | 工事B | 100円 | 2018 | 工事A | 200円 | 2018 |
1 | 工事B | 100円 | 2018 | 工事A | 500円 | 2017 |
1 | 工事C | 100円 | 2018 | 工事A | 300円 | 2018 |
1 | 工事C | 100円 | 2018 | 工事A | 100円 | 2017 |
2 | 工事A | 100円 | 2018 | 工事A | 100円 | 2018 |
2 | 工事A | 100円 | 2018 | 工事A | 500円 | 2017 |
2 | 工事B | 100円 | 2018 | 工事A | 200円 | 2018 |
2 | 工事B | 100円 | 2018 | 工事A | 500円 | 2017 |
2 | 工事C | 100円 | 2018 | 工事A | 300円 | 2018 |
2 | 工事C | 100円 | 2018 | 工事A | 100円 | 2017 |
3 | 工事A | 100円 | 2018 | 工事A | 100円 | 2018 |
3 | 工事A | 100円 | 2018 | 工事A | 500円 | 2017 |
3 | 工事B | 100円 | 2018 | 工事A | 200円 | 2018 |
3 | 工事B | 100円 | 2018 | 工事A | 500円 | 2017 |
3 | 工事C | 100円 | 2018 | 工事A | 300円 | 2018 |
3 | 工事C | 100円 | 2018 | 工事A | 100円 | 2017 |
このままじゃ、単に結合しただけなので、以下のように集計します。
①工事テーブルのNoを、支払テーブルとクロス結合して支払テーブルにNoを付与する。
②支払テーブルから工事名ごとに抽出し、Noを結合条件に工事テーブルと結合する
select sum(金額) as 支払合計
from (select No
from 工事テーブル
CROSS JOIN (select *
from 支払テーブル
)工事
left join (select 1 as No
,SA.*
from 支払テーブル SA
where 工事名 = "工事A"
)支払A
on 工事.No = 支払A.No
left join (select 2 as No
,SB.*
from 支払テーブル SB
where 工事名 = "工事B"
)支払B
on 工事.No = 支払B.No
left join (select 3 as No
,SC.*
from 支払テーブル SC
where 工事名 = "工事C"
)支払C
on 工事.No = 支払C.No
)合計
工事テーブルに存在する全工事の、支払レコードが重複なしにできます。
No | 工事名 | 金額 | 年度 |
---|---|---|---|
1 | 工事A | 100円 | 2018 |
1 | 工事A | 500円 | 2017 |
2 | 工事B | 200円 | 2018 |
2 | 工事B | 500円 | 2017 |
3 | 工事C | 300円 | 2018 |
3 | 工事C | 100円 | 2017 |
支払合計 1700円になり、重複なしの金額が集計できます。
まどろっこしい書き方をしましたが、レコードをSEQ毎に複製したいときなど、便利です。
今日はここまで。