使用LEFT JOIN 统计左右存在的数据

需求

企业表

企业表​​t_company​​有如下字段:标识​​id​​、企业名称​​name​​:

id

name

1

腾讯

2

百度

收款表

企业对应有收款表​​t_collection​​有如下字段:标识​​id​​、账套​​account​​、企业id​​company_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​​、企业id​​company_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

再分账套做汇总(重点)

在上面统计的基础上,再拆分账套统计

使用LEFT JOIN 统计左右存在的数据

收款表和发票表做账套的拆分,和企业表做关联:

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 统计左右存在的数据

这样就有一个问题:

如果左边表没有的数据,右边的表也不会查出来。比如以上查询收款表不存在的账套,发票表存在账套也不会查出来。这就是​​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

总结

  • 企业分组统计收款和发票表,只需要对企业做​​group by​​分组即可。
  • 企业和账套一起分组,​​left join​​只会统计左边存在的数据,而需要统计两边都存在的数据。
  • 使用​​union​​多表查询比较繁琐。
  • ​left join​​使用​​on 1 = 1​​查询不添加限制条件,查询所有公司的账套,再关联发票和收款。
发表评论

相关文章