MySQL SUM 在包含多个联接的查询中输出错误值

Question

我使用以下查询获取有关这些表的信息，但是 Defenderhit 和 Defenderdamage SUM 值将与第一个联接的行号的行数相乘。

表“战斗”:

battle_id   city_id     attacker    defender    battle_time 
1           07          6           0           1342918014

表“战斗命中”:

battle_id   family_id   user_id     hits    damage 
1           0           0           1000    50000
1           6           15          108     3816
1           6           2           81  2046
1           6           1           852 1344

MySQL 查询:

SELECT b.battle_id, b.city_id, b.attacker, b.defender, b.battle_time,
SUM(COALESCE(bh1.damage,0)) AS attackerdamage, SUM(COALESCE(bh2.damage,0)) AS defenderdamage,
SUM(COALESCE(bh1.hits,0)) AS attackerhit, SUM(COALESCE(bh2.hits,0)) AS defenderhit
FROM battles AS b
LEFT JOIN battlehits AS bh1 ON b.attacker = bh1.family_id
LEFT JOIN battlehits AS bh2 ON b.defender = bh2.family_id
WHERE b.battle_id=1
GROUP BY b.battle_id LIMIT 1

查询结果如下:

battle_id   city_id     attacker    defender    battle_time     attackerdamage  defenderdamage  attackerhit     defenderhit 
1           07          6           0           1342918014      7206            150000          1041            3000

正如您在表数据中看到的那样，defenderhit 和 Defenderdamage SUM 值应该是 1000 和 50000，但它们乘以 3。我在这里做什么？有什么问题吗？

提前致谢。

Answer 1

您将在 group by/sum 之前得到三行。战斗中的三排攻击者每一排都有一排。其中每一个都与来自战斗命中的相同防守者行配对，导致防守者数据增加三倍。要看到这一点，请删除 group by 和 limit 子句并取出 sum()。您实际上是在创建所有防御者 X 所有攻击者的叉积，然后求和。

这显示了具有重复防御者数据的三行。这是对一对多对多关系(而不是一对一对一)进行联接的结果。

SELECT b.battle_id, b.city_id, b.attacker, b.defender, b.battle_time,
COALESCE(bh1.damage,0) AS attackerdamage, COALESCE(bh2.damage,0) AS defenderdamage,
COALESCE(bh1.hits,0) AS attackerhit, COALESCE(bh2.hits,0) AS defenderhit
FROM battles AS b
LEFT JOIN battlehits AS bh1 ON b.attacker = bh1.family_id
LEFT JOIN battlehits AS bh2 ON b.defender = bh2.family_id
WHERE b.battle_id=1;

输出:

battle_id   city_id attacker    defender    battle_time attackerdamage  defenderdamage  attackerhit defenderhit
1   7   6   0   1342918014  3816    50000   108 1000
1   7   6   0   1342918014  2046    50000   81  1000
1   7   6   0   1342918014  1344    50000   852 1000

您需要将其拆分为单独的查询。一个用于攻击者的总和，另一个用于防御者的总和。