即使没有子记录，也需要视图来计算差异

Question

下面的视图应该计算总计和金额之间的差额。但是，如果在bill_pay_allocations中没有输入相应的付款，则视图无法计算。即使没有相应的付款，我怎样才能得到差价呢？

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之间使用左外部连接。使用Case语句查找Null值的数量，并将其替换为0。

如果必要的话，还可以在bill_payments上传播左边的外部联接。

请看下面的代码。

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`