使用LEFT JOIN 统计左右存在的数据 - 小码code
source link: https://www.cnblogs.com/jeremylai7/p/16820829.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
最近做了一个数据模块的统计,统计企业收款、发票相关的数据,开始统计是比较简单,后面再拆分账套统计就有点小复杂,本文做一个简单的记录。
企业表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
查询不添加限制条件,查询所有公司的账套,再关联发票和收款。
- 使用
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK