需求
企业表
企业表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 tcleft 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 tcleft 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.idunion
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 = 1id
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查询不添加限制条件,查询所有公司的账套,再关联发票和收款。