最近做了一个数据模块的统计 , 统计企业收款、发票相关的数据,开始统计是比较简单,后面再拆分账套统计就有点小复杂,本文做一个简单的记录 。需求企业表企业表t_company有如下字段:标识id、企业名称name:
idname1腾讯2百度收款表企业对应有收款表t_collection有如下字段:标识id、账套account、企业idcompany_id、收款金额amount:
idaccountcompany_idamount11130221203123042240开票表开票表t_invoice有如下字段:标识id、账套account、企业idcompany_id、发票金额amount:
idaccountcompany_idamount11110221203123042250汇总企业统计【使用LEFT JOIN 统计左右存在的数据】现在要做一个统计 , 统计企业收款金额,以及发票金额,需要将收款表和发票表将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_idleft join ( select company_id,sum(amount) as amount from t_invoice group by company_id) ti on tc.id = ti.company_id查询结果:
idnamecollection_amountinvoice_amunt1腾讯50302百度7080再分账套做汇总(重点)在上面统计的基础上 , 再拆分账套统计 。
![使用LEFT JOIN 统计左右存在的数据](http://img.zhejianglong.com/231018/21323U3V-0.png)
文章插图
收款表和发票表做账套的拆分,和企业表做关联:
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_idleft 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 统计左右存在的数据](http://img.zhejianglong.com/231018/21323VJ8-1.png)
文章插图
这样就有一个问题:
如果左边表没有的数据,右边的表也不会查出来 。比如以上查询收款表不存在的账套,发票表存在账套也不会查出来 。这就是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.idunionselect * 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 = 1idnameaccount1腾讯11腾讯22百度12百度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)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 and tc.account = tc2.accountleft 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
推荐阅读
- 使用 etcdadm 快速、弹性部署 etcd 集群
- Android RecyclerView使用ListAdapter高效刷新数据
- 使用 Windows Core Audio APs 进行 Loopback Recording 并生成 WAV 文件
- 4 .NET 6学习笔记——如何在.NET 6的Desktop App中使用Windows Runtime API
- 使用react+redux实现弹出框案例
- 你真的会使用Typora吗?
- vue3中$attrs的变化与inheritAttrs的使用
- win10本地python第三方库安装成功,但是pycharm项目无法使用解决方案
- 华为快充66w和40w有什么区别-使用对比
- 了解 Flutter 开发者们的 IDE 使用情况