一、什么是笛卡爾積問(wèn)題？

在 SQL 多表查詢中，如果表和表之間沒(méi)有正確的關(guān)聯(lián)條件，數(shù)據(jù)庫(kù)就會(huì)把一張表的每一行和另一張表的每一行互相組合。

例如：

select *
from table_a, table_b;

如果 table_a 有 10 條數(shù)據(jù)，table_b 有 20 條數(shù)據(jù)，最終結(jié)果就是：

10 × 20 = 200 條

這就是典型的笛卡爾積。

在實(shí)際開(kāi)發(fā)中，更常見(jiàn)的問(wèn)題不是完全忘記寫關(guān)聯(lián)條件，而是多個(gè)一對(duì)多表同時(shí)關(guān)聯(lián)，導(dǎo)致結(jié)果數(shù)量被放大。

比如：

主表：1 條
明細(xì)表 A：3 條
明細(xì)表 B：5 條

如果直接把三張表一起查：

select *
from main_table m
left join detail_a a on a.main_id = m.id
left join detail_b b on b.main_id = m.id;

結(jié)果可能會(huì)變成：

3 × 5 = 15 條 

原因是：detail_a 和 detail_b 都是主表的子表，它們之間沒(méi)有一一對(duì)應(yīng)關(guān)系，數(shù)據(jù)庫(kù)只能把兩邊明細(xì)互相組合。

這類問(wèn)題也可以理解為“笛卡爾積式行數(shù)放大”。

二、常見(jiàn)解決方案

1. 補(bǔ)全正確的 JOIN 條件

最基礎(chǔ)的情況是漏寫了關(guān)聯(lián)條件。

錯(cuò)誤寫法：

select *
from table_a a
join table_b b;

正確寫法：

select *
from table_a a
join table_b b on b.a_id = a.id;

每個(gè) join 都應(yīng)該有明確的關(guān)聯(lián)條件。

不過(guò)需要注意：

有 on 條件，不代表一定不會(huì)出現(xiàn)行數(shù)放大。

如果同時(shí)關(guān)聯(lián)多個(gè)一對(duì)多子表，仍然可能出現(xiàn)數(shù)據(jù)倍增。

2. 子表先聚合，再關(guān)聯(lián)主表

如果最終只需要匯總結(jié)果，比如數(shù)量、金額、次數(shù)，就不要直接關(guān)聯(lián)明細(xì)表。

可以先把子表聚合成一行，再關(guān)聯(lián)主表。

示例：

select
    m.id,
    a.total_amount
from main_table m
left join (
    select
        main_id,
        sum(amount) as total_amount
    from detail_a
    group by main_id
) a on a.main_id = m.id;

這樣 detail_a 原本可能有多條數(shù)據(jù)，但聚合后每個(gè) main_id 只剩一條，再關(guān)聯(lián)主表就不會(huì)放大結(jié)果。

適用場(chǎng)景：

只需要合計(jì)金額
只需要統(tǒng)計(jì)數(shù)量
只需要主表級(jí)別結(jié)果

3. 使用 EXISTS 判斷是否存在

如果只是判斷子表有沒(méi)有數(shù)據(jù)，不需要取子表字段，可以用 exists，不要用 join。

不推薦：

select distinct m.*
from main_table m
join detail_a a on a.main_id = m.id;

推薦：

select *
from main_table m
where exists (
    select 1
    from detail_a a
    where a.main_id = m.id
);

exists 只判斷是否存在，不會(huì)因?yàn)樽颖碛卸鄺l記錄而讓主表重復(fù)出現(xiàn)。

適用場(chǎng)景：

查詢有明細(xì)的數(shù)據(jù)
查詢存在某類記錄的數(shù)據(jù)
只做篩選，不展示子表字段

4. 使用 UNION ALL 拆開(kāi)不同明細(xì)

如果有多個(gè)明細(xì)表，并且它們之間沒(méi)有一一對(duì)應(yīng)關(guān)系，可以分開(kāi)查，再用 union all 合并。

比如：

主表 1 條
明細(xì) A 3 條
明細(xì) B 5 條

直接 join 會(huì)變成 15 條。

如果只是想把兩類明細(xì)放在同一個(gè)結(jié)果里展示，可以這樣：

select
    main_id,
    'A類明細(xì)' as row_type,
    amount
from detail_a

union all

select
    main_id,
    'B類明細(xì)' as row_type,
    amount
from detail_b;

union all 是上下合并，不會(huì)讓 A 明細(xì)和 B 明細(xì)互相組合。

結(jié)果類似：

main_id    row_type    amount
1          A類明細(xì)      100
1          A類明細(xì)      200
1          B類明細(xì)      300
1          B類明細(xì)      400

適用場(chǎng)景：

多個(gè)明細(xì)表沒(méi)有一一對(duì)應(yīng)關(guān)系
只是想分開(kāi)展示不同類型的數(shù)據(jù)
不想讓明細(xì)之間互相相乘

這個(gè)方案在報(bào)表類 SQL 中很常用。

5. 使用 ROW_NUMBER() 按順序?qū)R

有些情況下，確實(shí)需要把兩邊明細(xì)按順序放在同一行，可以使用 row_number() 給兩邊編號(hào)，然后按編號(hào)關(guān)聯(lián)。

思路是：

明細(xì) A 第 1 行  對(duì)應(yīng)  明細(xì) B 第 1 行
明細(xì) A 第 2 行  對(duì)應(yīng)  明細(xì) B 第 2 行
明細(xì) A 第 3 行  對(duì)應(yīng)  明細(xì) B 第 3 行

簡(jiǎn)單示例：

with a as (
    select
        main_id,
        amount,
        row_number() over(partition by main_id order by id) as rn
    from detail_a
),
b as (
    select
        main_id,
        amount,
        row_number() over(partition by main_id order by id) as rn
    from detail_b
)
select
    a.main_id,
    a.amount as amount_a,
    b.amount as amount_b
from a
left join b
    on b.main_id = a.main_id
   and b.rn = a.rn;

這樣可以避免：

A 明細(xì)數(shù)量 × B 明細(xì)數(shù)量

但是這個(gè)方案要謹(jǐn)慎使用。

因?yàn)樗皇前葱刑?hào)對(duì)齊，不代表兩邊數(shù)據(jù)真的有業(yè)務(wù)對(duì)應(yīng)關(guān)系。

適用場(chǎng)景：

業(yè)務(wù)上明確要求第 N 行對(duì)應(yīng)第 N 行
兩邊數(shù)據(jù)確實(shí)可以按順序匹配
只是為了報(bào)表展示排版

如果兩邊沒(méi)有真實(shí)對(duì)應(yīng)關(guān)系，更推薦使用 union all。

6. 子表先去重

有時(shí)結(jié)果重復(fù)是因?yàn)樽颖肀旧碛兄貜?fù)數(shù)據(jù)。

可以先去重，再關(guān)聯(lián)。

select distinct main_id, value
from detail_a;

或者在子查詢中先處理：

select *
from main_table m
left join (
    select distinct main_id, value
    from detail_a
) a on a.main_id = m.id;

適用場(chǎng)景：

子表存在重復(fù)記錄
中間關(guān)系表存在重復(fù)關(guān)系
只需要唯一結(jié)果

7. 拆成多個(gè)結(jié)果集，由程序?qū)咏M裝

有些數(shù)據(jù)本身就是層級(jí)結(jié)構(gòu)，不適合用一條 SQL 強(qiáng)行查完。

比如：

主表
  ├── 明細(xì)表 A
  ├── 明細(xì)表 B
  └── 明細(xì)表 C

如果多個(gè)明細(xì)表之間沒(méi)有一一對(duì)應(yīng)關(guān)系，全部寫在一條 SQL 里，很容易出現(xiàn)行數(shù)放大，也會(huì)讓 SQL 變得很難維護(hù)。

這種情況下，可以拆成多條 SQL：

SQL 1：查詢主表
SQL 2：查詢明細(xì)表 A
SQL 3：查詢明細(xì)表 B
SQL 4：查詢明細(xì)表 C

然后在 Java、Python或前端中，按照主表 ID 進(jìn)行組裝。

適用場(chǎng)景：

多個(gè)明細(xì)表之間沒(méi)有一一對(duì)應(yīng)關(guān)系
一條 SQL 寫起來(lái)很復(fù)雜
需要返回層級(jí)結(jié)構(gòu)數(shù)據(jù)
報(bào)表或接口展示邏輯比較復(fù)雜

這種方式可以避免為了“一條 SQL 查完”而強(qiáng)行 join 多個(gè)明細(xì)表。不過(guò)它會(huì)增加程序?qū)咏M裝邏輯，也可能增加查詢次數(shù)，需要結(jié)合數(shù)據(jù)量和性能要求綜合考慮。

三、如何選擇解決方案？

可以按下面的思路判斷：

最關(guān)鍵的是先確認(rèn)：

最終結(jié)果一行代表什么？

如果一行代表主表，就盡量不要直接展開(kāi)多個(gè)明細(xì)表。

如果一行代表某個(gè)明細(xì)，就要避免再關(guān)聯(lián)其他一對(duì)多明細(xì)。

如果多個(gè)明細(xì)沒(méi)有對(duì)應(yīng)關(guān)系，就不要強(qiáng)行橫向 join。

到此這篇關(guān)于SQL 多表聯(lián)查中的笛卡爾積問(wèn)題及解決方案的文章就介紹到這了,更多相關(guān)SQL 多表聯(lián)查笛卡爾積問(wèn)題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論