两个类似的mysql查询如何显示不同的结果？

Question

我有两个MySQL查询，我正试图使用这些查询来平衡数据库和查找问题。

查询1的预支总计为286940.99，查询2的总计为288645.3，这是1 704.31不同(未分配的预付款也是错误的)。

我遇到的问题是，它们都是同一列的求和，唯一的不同是查询2有一个左联接来加入另一个表。左联接根本不应影响主表。

这是我的疑问

查询1

  SELECT sum(advance) as advance, sum(advance_unallocated) as advance_unallocated FROM     `deals` 

查询2

SELECT 

pb.id as pbid,
d.id as did,
sum(d.advance) as advance,
sum(d.advance_unallocated) as advance_unallocated,
sum(pb.payments) as pb_payment,
sum(pb.payments) - sum(d.advance) - sum(d.advance_unallocated) as diff,
pb.payment_method as p_payment_method 

FROM `deals` d 

LEFT OUTER JOIN `payment_balance` pb on d.id = pb.link_id and pb.table_name = 'deals' 

Answer 1

之所以会出现这种情况，是因为LEFT JOIN返回的行数不是您最初认为的[0, 1]数，而是将符合连接条件的所有行都，或者用一行填充NULL。

在您的特殊情况下，有1行或更多行与联接条件“意外”匹配。