V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member Sign In
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 2398 days ago, the information mentioned may be changed or developed.
SELECT
sc.*,
sum( scdr.coal_weight * scdr.unit_price ) totalSpending,
sum( scrr.recharge_amount ) totalRecharge
FROM
self_contract sc,
self_contract_delivery_record scdr,
self_contract_recharge_record scrr
WHERE
sc.id = scdr.self_contract_id
AND sc.id = scrr.self_contract_id
AND sc.id = '080c8d912301f027a03214b89d9e4a0c'
这样 sum( scrr.recharge_amount )和 sum( scdr.coal_weight * scdr.unit_price )统计错误
会根据 self_contract_delivery_record 这张表匹配到了多少条记录 两个 sum 就都会 乘以几
请教应该如何写 （最好是 leftjoin 的这种，self_contract 主表无论匹不匹配到都要先展示)
sc.*,
sum( scdr.coal_weight * scdr.unit_price ) totalSpending,
sum( scrr.recharge_amount ) totalRecharge
FROM
self_contract sc,
self_contract_delivery_record scdr,
self_contract_recharge_record scrr
WHERE
sc.id = scdr.self_contract_id
AND sc.id = scrr.self_contract_id
AND sc.id = '080c8d912301f027a03214b89d9e4a0c'
这样 sum( scrr.recharge_amount )和 sum( scdr.coal_weight * scdr.unit_price )统计错误
会根据 self_contract_delivery_record 这张表匹配到了多少条记录 两个 sum 就都会 乘以几
请教应该如何写 （最好是 leftjoin 的这种，self_contract 主表无论匹不匹配到都要先展示)
4 replies • 2019-10-30 17:09:27 +08:00
 |
1
taogen Oct 15, 2019 via Android  1
盲猜
1. 添加 group by sc.id 2. 修改 sum( scdr.coal_weight * scdr.unit_price ) → sum( scdr.coal_weight ) * sum (scdr.unit_price) |
 |
2
superwhite Oct 25, 2019 1
刚刚也遇到了这个问题，在 sum 的里面加 distinct 即可，如 sum(distinct score)
|
 |
3
rqxiao OP 额 今天又碰到了 distinct 解决了
|
|
4
rqxiao OP @superwhite 额 今天又碰到了 distinct 解决了
|
Select Language