postgresql 减去不同表的两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30856311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtract two columns of different tables
提问by Nelson Jean Pierre
I have two unrelated tables:
我有两个不相关的表：
contribution(id,amount, create_at, user_id)
solicitude(id, amount, create_at, status_id, type_id, user_id)
I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative.
我需要减去用户的贡献和关心的总和，但结果不能是负数。
How can I do this? Function or query?
I tried this query:
我怎样才能做到这一点？函数还是查询？
我试过这个查询：
SELECT sum(contribution.amount)
- (SELECT sum(solicitude.amount)
FROM solicitude
WHERE user_id = 1 AND status_id = 1) as total
FROM contribution
WHERE contribution.user_id = 1
采纳答案by Erwin Brandstetter
I interpret your remark but that result can't to be negativeas requirement to return 0 instead of negative results. The simple solution is GREATEST():
我将您的评论解释but that result can't to be negative为要求返回 0 而不是否定结果。简单的解决方案是GREATEST()：
SELECT GREATEST(sum(amount)
- (SELECT sum(amount)
FROM solicitude
WHERE status_id = 1
AND user_id = 1), 0) AS total
FROM contribution
WHERE user_id = 1;
Otherwise, I kept your original query, which is fine.
否则，我保留了您的原始查询，这很好。
For other cases with the possible result that no row could be returned I would replace with twosub-selects. But the use of the aggregate function guaranteesa result row, even if the given user_idis not found at all. Compare:
对于可能导致无法返回任何行的其他情况，我将替换为两个子选择。但是使用聚合函数可以保证结果行，即使user_id根本找不到给定的行。比较：
If the result of the subtraction would be NULL(because no row is found or the sum is NULL), GREATEST()will also return 0.
如果减法的结果是NULL（因为没有找到行或总和是NULL），GREATEST()也将返回0。
回答by klin
You can add an outer query to check the total value:
您可以添加外部查询来检查总值：
SELECT CASE WHEN total > 0 THEN total ELSE 0 END AS total
FROM (
SELECT
sum(contribution.amount) - (SELECT sum(solicitude.amount)
FROM solicitude
WHERE user_id = 1 AND status_id = 1) as total
FROM contribution
WHERE
contribution .user_id = 1
) alias;
This solution is OK, but I suggest an alternative approach. Check how this query works:
这个解决方案没问题，但我建议另一种方法。检查此查询的工作原理：
with contribution as (
select user_id, sum(amount) as amount from contribution
group by 1),
solicitude as (
select user_id, sum(amount) as amount from solicitude
where status_id = 1
group by 1)
select
c.user_id, c.amount as contribution, s.amount as solitude,
case when c.amount > s.amount then c.amount - s.amount else 0 end as total
from contribution c
join solicitude s on c.user_id = s.user_id;
I made a simple test, just out of curiosity, on this setup:
出于好奇，我对这个设置做了一个简单的测试：
create table public.solicitude (
id integer,
amount numeric,
create_at timestamp without time zone,
status_id integer,
type_id integer,
user_id integer
);
create table public.contribution (
id integer,
amount numeric,
create_at timestamp without time zone,
user_id integer
);
insert into contribution (user_id, amount)
select (random()* 50)::int, (random()* 100)::int
from generate_series(1, 4000000);
insert into solicitude (user_id, amount, status_id)
select (random()* 50)::int, (random()* 100)::int, 1
from generate_series(1, 4000000);
Results (msecs):
结果（毫秒）：
Erwin's solution with greatest(): 922, 905, 922, 904, 904, 904, 905, 912, 905, 922
My solution with an outer query: 796, 795, 814, 814, 815, 795, 815, 796, 815, 796
回答by jurhas
You have to join the tables
你必须加入表格
SELECT sum(c.amount) - s.total
FROM contribution c inner join (SELECT user_id, sum(solicitude.amount) total
FROM solicitude GROUP BY c.user_id HAVING user_id = 1 AND status_id = 1 )s
on c.user_id=s.user_id GROUP BY c.user_id,s.total HAVING c.user_id = 1
EDIT: I forgot an alias
编辑：我忘记了别名
