mysql - 即使没有子记录也需要 View 来计算差异

Question

以下 View 应该计算“总计”和“金额”之间的差异。但如果 bill_pay_allocations 中没有输入相应的付款，则 View 将无法计算。即使没有相应的付款，我怎样才能获得计算差额？

SELECT
`alphabase`.`bill_ing_sheets`.`INVOICE_NO` AS `INVOICE_NO`,
`alphabase`.`bill_ing_sheets`.`TOTAL` AS `TOTAL`,
`alphabase`.`bill_pay_allocations`.`AMOUNT` AS `AMOUNT`,
`alphabase`.`bill_pay_allocations`.`AMOUNT` - `alphabase`.`bill_ing_sheets`.`TOTAL` AS `Difference` 
FROM
`alphabase`.`bill_ing_sheets` left join `alphabase`.`bill_pay_allocations` 
ON
`alphabase`.`bill_pay_allocations`.`BILLING_ID` = `alphabase`.`bill_ing_sheets`.`BILLING_ID` 
left join `alphabase`.`bill_payments` 
ON
`alphabase`.`bill_payments`.`PAYMENT_ID` = `alphabase`.`bill_pay_allocations`.`PAYMENT_ID`

Answer 1

您应该在 bill_ing_sheets 和 bill_pay_allocations 之间使用 LEFT OUTER JOIN。使用 Case 语句查找 Amount 中的 Null 值并将其替换为 0。

如果 bill_ payment 上有必要，还可以传播 LEFT OUTER JOIN。

请参阅下面的代码。

SELECT
`alphabase`.`bill_ing_sheets`.`INVOICE_NO` AS `INVOICE_NO`,
`alphabase`.`bill_ing_sheets`.`TOTAL` AS `TOTAL`,
(case when `alphabase`.`bill_pay_allocations`.`AMOUNT` is not null then `alphabase`.`bill_pay_allocations`.`AMOUNT` else 0 end) AS `AMOUNT`,
(case when `alphabase`.`bill_pay_allocations`.`AMOUNT` is not null then `alphabase`.`bill_pay_allocations`.`AMOUNT` else 0 end) - `alphabase`.`bill_ing_sheets`.`TOTAL` AS `Difference` 
FROM
`alphabase`.`bill_ing_sheets` left outer join `alphabase`.`bill_pay_allocations` 
ON
`alphabase`.`bill_pay_allocations`.`BILLING_ID` = `alphabase`.`bill_ing_sheets`.`BILLING_ID` 
left outer join `alphabase`.`bill_payments` 
ON
`alphabase`.`bill_payments`.`PAYMENT_ID` = `alphabase`.`bill_pay_allocations`.`PAYMENT_ID`