mysql - 两次连接同一张表时 Sum 返回错误值

Question

我有一个 parent和一个 child表和 child表可以通过分配给 ref_id 的外键引用自身专栏。

我正在尝试执行一个求和，它将返回父 ID (parent_id) 和 value 的总和它的子列，加上 value 的总和在 ref_id 中引用值的行上的列指向当前父级的子级。另一方面，ref_id 所在的行is not null 不应该被添加到他们自己父级的总和中。

但是，当我执行求和时，它会将每个引用的子项的值乘以引用的次数。例如，如果 id 为 1 的 child 和值(value)11.35被引用三次，34.05添加到总和而不是 11.35 .

这是我的结构:

create table parent(
  parent_id int auto_increment primary key
);

create table child(
  child_id int auto_increment primary key,
  parent_id int not null,
  value decimal(19,2) not null,
  ref_id int null,
  foreign key (parent_id) references parent (parent_id),
  foreign key (ref_id) references child (child_id)
);

这是我的示例数据:

parent: (1), (2)

child:
    (1, 1, 11.35, null),
    (2, 1, 38.37, null),
    (3, 1, 65.46, null),
    (4, 2, 289.42, 1),
    (5, 2, 978.35, 1),
    (6, 2, 1669.19, 1)

当我执行这个查询时:

select p.parent_id as parent_id, (ifnull(sum(c1.value), 0) + ifnull(sum(c2.value), 0)) as total
from parent as p
left join child as c1 on (p.parent_id = c1.parent_id and c1.ref_id is null)
left join child as c2 on (c1.child_id = c2.ref_id)
group by p.parent_id;

我期望这样的结果:

parent_id: 1, total: 3052.14
parent_id: 2, total: 0.00

相反，我得到了这个结果:

parent_id: 1, total: 3074.84
parent_id: 2, total: 0.00

为什么？我该如何解决？

编辑:这是我的问题的 fiddle :http://sqlfiddle.com/#!9/a7fedf/1/0

Answer 1

值 11.35 被复用三次用于 c1.child_id = c2.ref_id 在你的情况下匹配条件，差异 34.05 是由于对此。考虑分别连接这两个表两次，然后使用 union 合并:

select sum(q.val) from
(
select (ifnull(c2.value, 0) ) as val
  from child as c1 
  left join child as c2 on c1.child_id = c2.ref_id  
union all
select ( ifnull(c.value, 0))
  from parent as p 
  left join child as c on p.parent_id = c.parent_id and c.ref_id is null 
) q 

Demo