php - 如何从多个表中选择mysql SUM并从另一个表中选择其他列？

Question

我有三个表:现金、支票和账单。所有三个表共享两个公共(public)列:billId 和 customerId。

账单表

+--------+------------+---------+------------+
| billId | date       | bAmount | customerId |
+--------+------------+---------+------------+
| #1     | 01-05-2016 | 120.00  | 2          |
+--------+------------+---------+------------+
| #2     | 10-05-2016 | 100.00  | 2          |
+--------+------------+---------+------------+
| #3     | 20-05-2016 | 80.00   | 2          |
+--------+------------+---------+------------+
| #4     | 20-05-2016 | 70.00   | 2          |
+--------+------------+---------+------------+
| #5     | 27-05-2016 | 50.00   | 2          |
+--------+------------+---------+------------+
| #6     | 28-05-2016 | 20.00   | 2          |
+--------+------------+---------+------------+

支票表

+----------+--------+------------+----------+
| chequeId | billId | customerId | chAmount |
+----------+--------+------------+----------+
| 1        | #1     | 2          | 50.00    |
+----------+--------+------------+----------+
| 2        | #2     | 2          | 25.00    |
+----------+--------+------------+----------+
| 3        | #5     | 2          | 36.00    |
+----------+--------+------------+----------+
| 4        | #4     | 2          | 23.00    |
+----------+--------+------------+----------+

现金表

+--------+--------+------------+----------+
| cashId | billId | customerId | caAmount |
+--------+--------+------------+----------+
| 1      | #1     | 2          | 55.00    |
+--------+--------+------------+----------+
| 2      | #2     | 2          | 70.00    |
+--------+--------+------------+----------+
| 3      | #3     | 2          | 69.00    |
+--------+--------+------------+----------+
| 4      | #4     | 2          | 23.00    |
+--------+--------+------------+----------+

我必须生成一个查询才能生成如下结果:

+--------+------------+--------+---------+
| billId | date       | amount | pending |
+--------+------------+--------+---------+
| #1     | 01-05-2016 | 120.00 | 15.00   |
+--------+------------+--------+---------+
| #2     | 10-05-2016 | 100.00 | 05.00   |
+--------+------------+--------+---------+
| #3     | 20-05-2016 | 80.00  | 11.00   |
+--------+------------+--------+---------+
| #4     | 20-05-2016 | 70.00  | 14.00   |
+--------+------------+--------+---------+
| #5     | 27-05-2016 | 50.00  | 04.00   |
+--------+------------+--------+---------+

我正在从另一个页面向此页面发送customerID的值，例如$customerId = $_REQUEST['customerId']，并且从中我必须选择<账单表中的strong>BillId和日期、金额(由chAmount+caAmount之和计算得出)，以及待处理(通过bAmount-(chAmount+caAmount)的差值计算)。由于 billId #6 在支票和现金表中没有任何记录，因此不需要在结果中生成。请提及一个正确的 MySql 查询并解释它。

Answer 1

通常情况下，你应该尝试一些东西，但由于我正处于“美好时光”，所以我编写了完整的 SQL 语句:

SELECT b.billId, b.bDate, b.bAmount, SUM(ch.chAmount) chAmountSum, SUM(ca.caAmount) caAmountSum, (b.bAmount - ( IFNULL(SUM(ch.chAmount), 0)  + IFNULL(SUM(ca.caAmount), 0))) pending
FROM bill b
LEFT JOIN  cheque ch on ch.billId = b.billId
LEFT JOIN  cash ca on ca.billID = b.billId
GROUP BY b.billID;

• 您必须选择要显示的所有列
• 对于最后一个(待处理)，请进行操作(金额 - (支票 + 现金))，如果它是空值，则通过 IFNULL SQL 函数将其替换为“0”
• 使用 LEFT JOIN 因为您想要所有账单，即使还没有关联的付款(支票/现金)
• GROUP BY 来自您的引用列:billID