MySQL - 来自另一个表的总和

Question

我想运行一个包含来自另一个表的总和的选择语句。我知道我可能必须设置一个连接，但我不确定如何处理它。

我想选择一个账户列表然后获取他们的余额(来自另一个表的总和)这是我一直以来的想法，但显然是不正确的。

SELECT
    account_name
FROM
    tblaccounts
    JOIN (
        SELECT
            SUM(balance) AS account_balance
        FROM
            tblinvoices
    ) t ON t.account_id = tblaccount_account_id
WHERE
    tblaccounts.account_id = 1

期望的输出

Name   |   balance------------------Account1      50Account2     100

Answer 1

尝试

SELECT account_name, SUM(balance) balance
 FROM tblaccounts a LEFT JOIN tblinvoices i
   ON a.account_id = i.tblaccount_account_id
WHERE a.customer_id = 1
GROUP BY account_id

输出:

| ACCOUNT_NAME | BALANCE |--------------------------|     Account1 |      50 ||     Account2 |     100 |

这是 SQLFiddle 演示