mysql - 获取引用表的总计列

考虑一个帐户和存款数据库：

CREATE TABLE accounts (
    id int not null primary key,
    name varchar(63)
);

CREATE TABLE deposits (
    id int not null primary key,
    account int references accounts(id),
    dollars decimal(15, 2),
    status enum('pending','complete')
);


insert into accounts values
(0, 'us'),
(1, 'europe'),
(2, 'asia');

insert into deposits values
(0, 0, 10, 'pending'),
(1, 0, 20, 'complete'),
(2, 1, 100, 'complete'),
(3, 1, 200, 'pending'),
(4, 1, 300, 'complete'),
(5, 2, 1000, 'pending');

我希望获得每个银行的所有complete存款总额，这是预期的结果：

+--------+-----+
| us     | 20  |
| europe | 400 |
| asia   | 0   |
+--------+-----+

这是我尝试过的SQL，但无法按预期工作：

SELECT
    a.name, SUM(d.dollars)
FROM
    accounts a
  INNER JOIN
    deposits d ON (a.id = d.account AND d.status='complete');

这是它给的结果：

+--------+-----+
| us     | 420 |
+--------+-----+

当前代码的Here is an SQLfiddle。

我做错了什么，如何得到期望的金额？

最佳答案

尝试这个

 SELECT
a.name, coalesce(SUM(d.dollars),0) as sums
FROM
accounts a
left JOIN
deposits d ON (a.id = d.account AND d.status='complete')
group by  a.name
order by sums desc

您应该使用LEFT JOIN，也应该使用GROUP BY。

LOOK DEMO

关于mysql - 获取引用表的总计列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21934916/