最近做了一個資料模組的統計，統計企業收款、發票相關的資料，開始統計是比較簡單，後面再拆分賬套統計就有點小複雜，本文做一個簡單的記錄。

需求

企業表

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

收款表

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

開票表

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

彙總企業統計

現在要做一個統計，統計企業收款金額，以及發票金額，需要將收款表和發票表將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

查詢結果：

再分賬套做彙總（重點）

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

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

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

查詢出所有的公司賬套之後，再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

結果：

總結

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

參考

• sql left join on 條件不寫可以嗎 會怎麼樣

• mysql中的幾種join 及 full join，自然連線問題