mysql - 所有账户的账户交易总和

Question

我正在 MySQL 中跟踪交易，并且我正在尝试整理一份报告以显示给定月份每个帐户的总数。现在，我可以让它显示任何有交易的账户的总计，但问题是我需要它也返回没有交易的账户，而当前查询没有这样做。

我的数据库设计有两张表，一张用于帐户(每个帐户都有一个名称和 ID)，还有一张交易表(带有 ID、accountId、日期和金额字段)。我当前的查询如下所示:

SELECT 
    SUM(transactions.amount) AS monthly_total,
    accounts.id AS account_id,
    accounts.name AS account_name
FROM accounts
LEFT JOIN transactions ON transactions.accountId = accounts.id
WHERE transactions.date BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY accounts.id
ORDER BY accounts.name

正如我所说，除了不返回所有帐户外，此查询有效。它只返回有交易记录的账户。有什么想法吗？

Answer 1

您的WHERE 条件正在过滤掉没有交易的帐户。

尝试 -

SELECT 
    SUM(transactions.amount) AS monthly_total,
    accounts.id AS account_id,
    accounts.name AS account_name
FROM accounts
LEFT JOIN transactions ON transactions.accountId = accounts.id 
        AND transactions.date BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY accounts.id
ORDER BY accounts.name