我有 3 個表，“帳戶”“費用”和“付款”我想創建一個包含帳戶資料、費用總和和付款總和的視圖，但付款的記錄通常比同一帳戶的費用多當我創建視圖時，GROUP BY 費用最終會“重復”，就像行數不匹配一樣。y 嘗試使用 COALLECE，但仍按付款次數重復收費。這是查詢：
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`,
sum(`c`.`subtotal`) AS `monto`,
sum(`a`.`monto`) AS `abonos`
from ((`mvcdb`.`nota` `n`
left join `mvcdb`.`concepto` `c` on((`n`.`id` = `c`.`nota_id`)))
left join `mvcdb`.`abono` `a` on((`n`.`id` = `a`.`nota_id`)))
group by `n`.`id`
也試過用collece
...coalesce(sum(`c`.`subtotal`),0) AS `monto`,
coalesce(sum(`a`.`monto`),0) AS `abonos`...
希望我簡潔（足夠）我用我的母語嘗試了 StackOverflow，但被遺棄了
uj5u.com熱心網友回復：
您可以使用兩個子選擇：
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`,
(select sum(subtotal) from `mvcdb`.`concepto` c on c.nota_id = n.id) as monto,
(select sum(`mbcdb`.abono`) from `mvcdb`.`nota` a on a.nota_id = n.id) AS abonos
from `mvcdb`.`nota` `n`
group by `n`.`id`
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/530728.html