PostgreSQL SUM不返回0行的值
PostgreSQL SUM不返回0行的值
提问于 2018-06-10 02:48:28
我有一个事务表，每个事务都有一个地址字段，它引用地址表中的一行，地址表中的每个地址都有一个coinID。
我想得到一个特定用户的每个硬币的所有交易的总和。
我的问题是，如果用户有0个交易或地址，属于一个特定的硬币，它完全从结果中消失。我需要在一个硬币表中的所有硬币，有0交易或地址返回与总和为0。
SELECT coins.name, SUM(transactions.amount),coins.price_usd
FROM coins
LEFT JOIN addresses ON addresses.coin_id = coins.id
LEFT JOIN transactions ON transactions.address = addresses.address
LEFT JOIN users ON transactions.user_id = users.id
WHERE users.email = 'testemail@email.com'
GROUP BY coins.name, coins.price_usd回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-10 03:11:06
此解决方案向您展示了您想要的：
create table coins (
id int,
name varchar(10),
price_usd int
);
insert into coins (id, name, price_usd) values (1, 'Pound', 2);
insert into coins (id, name, price_usd) values (2, 'Yen', 98);
insert into coins (id, name, price_usd) values (3, 'Euro', 3);
create table addresses (
coin_id int,
address int
);
insert into addresses (coin_id, address) values (1, 20);
insert into addresses (coin_id, address) values (3, 30);
create table transactions (
address int,
user_id int,
amount int
);
insert into transactions (address, user_id, amount) values (20, 500, 123);
insert into transactions (address, user_id, amount) values (20, 500, 101);
insert into transactions (address, user_id, amount) values (30, 501, 456);
create table users (
id int,
email varchar(50)
);
insert into users (id, email) values (500, 'testemail@email.com');
insert into users (id, email) values (501, 'another@email.com');
select coins.name, sum(transactions.amount),coins.price_usd
from coins
join addresses on addresses.coin_id = coins.id
join transactions on transactions.address = addresses.address
join users on transactions.user_id = users.id
where users.email = 'testemail@email.com'
group by coins.name, coins.price_usd
union all
select name, 0, 0
from coins
where id not in (
select coin_id
from addresses
join transactions on transactions.address = addresses.address
join users on transactions.user_id = users.id
where users.email = 'testemail@email.com'
);结果：
name sum price_usd
----- --- ---------
Pound 224 2 -- the requested user
Yen 0 0 -- another user
Euro 0 0 -- coin with no transactions页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50777492
复制相关文章
相似问题