减去两列不同的表

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

3 个解决方案

#1

I interpret your remark but that result can't to be negative as requirement to return 0 instead of negative results. The simple solution is GREATEST():

我解释你的评论,但结果不能为负,因为要求返回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 two sub-selects. But the use of the aggregate function guarantees a result row, even if the given user_id is not found at all. Compare:

对于可能导致无法返回行的其他情况,我将替换为两个子选择。但是使用聚合函数可以保证结果行,即使根本找不到给定的user_id也是如此。相比:

Get n grouped categories and sum others into one

获得n个分组的类别并将其他类别合并为一个

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。

#2

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

#3

-1

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

编辑:我忘了别名

#1