mysql算一个表的总和_mysql-获取多个表的总和

表示例：

branch

----------------------------------

branchID | branchName | brMgrName|

----------------------------------

| 1 | a |Tom |

| 2 | b |Jeff |

| 3 | c |Lin |

----------------------------------

order

----------------------

orderID | branchID |

----------------------

| 1 | 2 |

| 2 | 2 |

| 3 | 3 |

----------------------

payment

--------------------------------

paymentID | orderID | p-amount |

--------------------------------

| 1 | 1 | 50.00 |

| 2 | 2 | 126.00 |

| 3 | 3 | 50.00 |

--------------------------------

我想要的输出是

-------------------------------------------

| branchName | brMgrName | Total Amount |

-------------------------------------------

| a | Tom | 0.00 |

| b | Jeff | 252.00 |

| c | Lin | 50.00 |

-------------------------------------------

我试图确定如何总结3个不同表中每个分支的金额.

在输出中：

-branchName’a’在订单表中没有任何值,

-branchName’b’是通过PaymentID 1& 2,与同

-branchName’c’

通过加入分支和付款表,我已经成功返回了branchName和总金额：

SELECT `order`.branchID, sum(`p-amount`)

FROM `order`

JOIN payment

ON `order`.orderID = payment.orderID

GROUP BY branchID

但是,当我尝试以下代码时,它返回错误的值,并且仅选择了1行.

SELECT branchName, brMgrName, sum(`p-amount`)

FROM branch, payment

JOIN (SELECT `order`.branchID, sum(`p-amount`)

FROM `order`

JOIN payment

ON `order`.orderID = payment.orderID

GROUP BY branchID) b

ON b.branchID = branchID;

很感谢任何形式的帮助.

解决方法:

尝试这个：

SELECT b.branchName, brMgrName, sum(p.`p-amount`)

FROM branch b

LEFT JOIN `order` o

ON b.branchID = o.branchID

LEFT JOIN payment p

ON o.orderID = p.orderID

GROUP BY b.branchName, brMgrName

ORDER BY 3 -- third column on select list

标签：mysql

来源： https://codeday.me/bug/20191119/2034147.html