使用LEFT JOIN 統計左右存在的資料問題

2022-10-26 14:03:17

需求

企業表

企業表t_company有如下欄位：標識id、企業名稱name:

id	name
1	騰訊
2	百度

收款表

企業對應有收款表t_collection有如下欄位：標識id、賬套account、企業idcompany_id、收款金額amount：

id	account	company_id	amount
1	1	1	30
2	2	1	20
3	1	2	30
4	2	2	40

開票表

開票表t_invoice有如下欄位：標識id、賬套account、企業idcompany_id、發票金額amount：

id	account	company_id	amount
1	1	1	10
2	2	1	20
3	1	2	30
4	2	2	50

彙總企業統計

現在要做一個統計，統計企業收款金額，以及發票金額，需要將收款表和發票表將company_id做group up操作。開票表也是做類似的操作，企業表和上面的結果做left join連線操作，sql如下：

select tc.id,tc.name,tc2.amount as collection_amount,ti.amount as invoice_amunt from t_company tc 
left join (
  select company_id,sum(amount) as amount from t_collection group by company_id
) tc2 on tc.id = tc2.company_id
left join (
  select company_id,sum(amount) as amount from t_invoice group by company_id
) ti on tc.id = ti.company_id

查詢結果：

id	name	collection_amount	invoice_amunt
1	騰訊	50	30
2	百度	70	80

再分賬套做彙總（重點）

在上面統計的基礎上，再拆分賬套統計。

收款表和發票表做賬套的拆分，和企業表做關聯：

select tc.id,tc.name,tc2.amount as collection_amount,ti.amount as invoice_amunt from t_company tc 
left join (
  select company_id,account,sum(amount) as amount from t_collection 
  group by company_id,account
) tc2 on tc.id = tc2.company_id
left join (
  select company_id,account,sum(amount) as amount from t_invoice 
  group by company_id,account
) ti on tc.id = ti.company_id and tc2.account = ti.account

首先是將收款表做賬套的拆分，然後關聯發票表的賬套拆分。看似沒有問題，但是left join返回左邊的所有記錄，以及右邊欄位相等的資料。

這樣就有一個問題：

如果左邊表沒有的資料，右邊的表也不會查出來。比如以上查詢收款表不存在的賬套，發票表存在賬套也不會查出來。這就是left join的侷限性。

全表連線解決方案一：

MySQL有left join、right join應該也有full join全表連線。

但是MySQL是不支援full join全表連線。

網上也有解決方案使用union替換full_join,思路是左表左連線右邊，左表右連線右邊，將上面的兩個結果union連線起來：

select * from t1 left join t2 on t1.id = t2.id
union 
select * from t1 right join t2 on t1.id = t2.id;

上面只是兩個表的關聯，如果三個表或者更多的關聯，寫起來就比較繁瑣了。

全表連線解決方案二：

全表連線就是一個沒有限制的左表連線，就是去掉on關聯條件，

要left join所有的賬套，首先要顯示全所有的賬套，企業表關聯賬套表，但是兩個表是沒有關聯的，需要去掉on後面的關聯條件,但是MySQL語法連線後面必須要加on，將約束條件改成1 = 1即可：

 select tc.id,tc.name,ta.id as account from t_company tc left join t_account ta on 1 = 1

id	name	account
1	騰訊	1
1	騰訊	2
2	百度	1
2	百度	2

查詢出所有的公司賬套之後，再left join收款表和發票表:

select tc.id,tc.name,tc.account,tc2.amount as collection_amount,ti.amount as invoice_amunt from (
select tc.id,tc.name,ta.id as account from t_company tc left join t_account ta on 1 = 1
)tc
left join (
  select company_id,account,sum(amount) as amount from t_collection group by company_id,account
) tc2 on tc.id = tc2.company_id and tc.account = tc2.account
left join (
  select company_id,account,sum(amount) as amount from t_invoice group by company_id,account
) ti on tc.id = ti.company_id and tc.account = ti.account

結果：

id	name	account	collection_amount	invoice_amunt
1	騰訊	1	30	10
1	騰訊	2	20	20
2	百度	1	30	30
2	百度	2	40	50

總結

企業分組統計收款和發票表，只需要對企業做group by分組即可。
企業和賬套一起分組，left join只會統計左邊存在的資料，而需要統計兩邊都存在的資料。
- 使用union多表查詢比較繁瑣。
- left join使用on 1 = 1查詢不新增限制條件，查詢所有公司的賬套，再關聯發票和收款。