我有 4 张桌子：
1-供应商
| ID_A | 供应商名称 |
|---|---|
| 1 | 苹果 |
| 2 | 小米 |
| 3 | 诺基亚 |
| 4 | OPPO |
2-开始余额
| ID_B | 起始余额 |
|---|---|
| 1 | 1000 |
| 2 | 1000 |
| 3 | 1000 |
| 4 | 空 |
3- 发票
| ID_C | 发票值 |
|---|---|
| 1 | 200 |
| 1 | 500 |
| 2 | 800 |
| 3 | 250 |
| 3 | 400 |
| 4 | 空 |
4-返回
| ID_D | Return_value |
|---|---|
| 1 | 100 |
| 2 | 50 |
| 2 | 25 |
| 3 | 空 |
| 4 | 空 |
结果显示数据的算术方法：
起始余额 + 发票 - 退货 = 最终余额
我尝试在 mysql 中使用 UNION 和 JOINS ：
SELECT null , Supplier_name , ID_A , SUM(Invoice_value) , null , null FROM Suppliers
inner JOIN Invoices
ON ID_A = ID_C
group by ID_A
UNION ALL
SELECT null , Supplier_name , ID_A , null , SUM(Return_value), null FROM Suppliers
left JOIN Returns
ON ID_A = ID_D
group by ID_A
UNION ALL
SELECT Start Balance , Supplier_name, ID_A , null , null ,( Start Balance + ifnull(SUM(Invoice_value),0) - ifnull(SUM(Return_value),0) ) FROM Suppliers
left JOIN Start Balance
ON ID_A = ID_B
left JOIN Invoices
ON ID_A = ID_C
left JOIN Returns
ON ID_A = ID_D
group by ID_A
我预计这个结果是：
| 起始余额 | 供应商名称 | ID_A | 发票值 | Return_value | End_Balance |
|---|---|---|---|---|---|
| 1000 | 苹果 | 1 | 700 | 100 | 1600 |
| 1000 | 小米 | 2 | 800 | 75 | 1725 |
| 1000 | 诺基亚 | 3 | 650 | 空 | 1650 |
| 空 | OPPO | 4 | 空 | 空 | 空 |
但效果不佳，它在不同的行中显示结果，并且最终余额的计算是错误的 请问显示此结果的正确代码是什么
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
您可以对子查询中的总和进行求和并将它们连接在一起
SELECT `Start Balance`, Supplier_name , ID_A , `Invoice_value` , `Return_value` , `Start Balance` + IFNULL(Invoice_value,0) - ifnull(Return_value,0) total FROM Suppliers left JOIN Start_Balance ON ID_A = ID_B left JOIN ( SELECT `ID_C`, SUM(`Invoice_value`) `Invoice_value` FROM Invoices GROUP By ID_C) i ON ID_A = ID_C left JOIN ( SELECT `ID_D`, SUM(`Return_value`) `Return_value` FROM Returns GROUP BY `ID_D`) r ON ID_A = ID_D小提琴