如何连接三个具有where条件的表，以获得1M数据快速查询的两个关系表数量之和
如何连接三个具有where条件的表，以获得1M数据快速查询的两个关系表数量之和
提问于 2021-09-22 19:28:01
我有三张桌子: super_consumers，balance_ins，充电
super_consumers表：
id name
---------- ----
1 A
2 Bbalance_ins表：
id super_consumer_id amount type(1,2)
--- ----------------- ------ ----
1 1 10 1
2 1 20 1
3 2 10 2再收费表：
id super_consumer_id amount status(0,1)
--- ----------------- ------ ------
1 1 5 1
2 1 10 1
3 2 15 0我想得到每个super_consumers表的总量余额()和总充值()总量，如下所示，
- Total_balance_in金额来自balance_in表，其中类型为==，1
- Total_recharge来自于状态== 1
的再收费表
预期结果:
[
'id' = 1,
'name' = 'A',
'total_balance_in' = 30,
'total_recharge' = 15
],
[
'id' = 2,
'name' = 'B',
'total_balance_in' = 0,
'total_recharge' = 0
]如何用“laravel查询”或“mysql”正确地编写此查询?
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-09-22 20:05:45
尝试：
select super_consumers.id ,
super_consumers.name,
IF(tot_balance_amount IS NULL, 0 , tot_balance_amount) as tot_balance_amount,
IF(tot_recharge_amount IS NULL ,0, tot_recharge_amount) as tot_recharge_amount
from super_consumers
left join (select super_consumer_id,sum(amount) as tot_balance_amount
from balance_ins
where type='1'
group by super_consumer_id ) as a
on super_consumers.id=a.super_consumer_id
left join ( select super_consumer_id,sum(amount) as tot_recharge_amount
from recharges
where `status`='1'
group by super_consumer_id ) as b
on super_consumers.id=b.super_consumer_id;演示：https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/44
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69290205
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号