如何创建含双表求和的视图？解决行不匹配引发的数值重复问题

问题根源 #

直接关联nota、concepto、abono三张表时，concepto的每条记录会与abono的每条记录生成笛卡尔积。比如某账户有1条concepto记录、3条abono记录，关联后会得到3条重复的concepto数据，最终sum(c.subtotal)会被重复计算3次，导致结果虚高。COALLECE仅能处理null值，无法解决笛卡尔积引发的求和重复问题。

修正后的视图创建语句 #

create view v_notas as
select 
    `n`.`id` AS `id`,
    `n`.`cliente` AS `cliente`,
    `n`.`fecha` AS `fecha`,
    `n`.`tel` AS `tel`,
    `n`.`dir` AS `dir`,
    `n`.`status` AS `status`,
    coalesce(`c`.`monto`, 0) AS `monto`,
    coalesce(`a`.`abonos`, 0) AS `abonos`
from `mvcdb`.`nota` `n`
left join (
    -- 先按nota_id分组计算总charges
    select `nota_id`, sum(`subtotal`) AS `monto`
    from `mvcdb`.`concepto`
    group by `nota_id`
) `c` on `n`.`id` = `c`.`nota_id`
left join (
    -- 先按nota_id分组计算总payments
    select `nota_id`, sum(`monto`) AS `abonos`
    from `mvcdb`.`abono`
    group by `nota_id`
) `a` on `n`.`id` = `a`.`nota_id`;

说明 #

1. 先对concepto和abono分别按nota_id分组求和，得到每个账户的独立总金额，从根源避免关联时产生笛卡尔积。
2. 用coalesce(..., 0)确保无对应记录的账户显示0而非null，保证视图数据的完整性。

内容的提问来源于stack exchange，提问作者Arturo