我有两个表:一个用于业务,另一个用于交易。
create table businesses (id serial, name text);
create table transactions (id serial, src integer, dst integer, amount decimal);
insert into businesses (name) values ('John'); // id 1
insert into businesses (name) values ('Jane'); // id 2
-- Here, john gave 100 to jane
insert into transactions (src, dst, amount) values (1, 2, 100.0);
-- And here jane gave 50 to john
insert into transactions (src, dst, amount) values (2, 1, 50.0);
-- The balance between john and jane would be 50 positive for john and 50 negative for jane因此,将钱给对方的业务存储在src列中,而接收钱的业务存储在dst列中。这样我就知道谁给了钱,谁收了钱。
现在,假设我想要将此交易显示为一种分类帐或两个业务之间的简单余额报表,查询将是什么样子?因为这取决于交易的哪一方。如果它在A一侧,它是一种费用,如果它在B一侧,它就是一种收入。
发布于 2020-08-22 05:35:53
您将“取消透视”数据并进行聚合:
select src, dest, sum(amount)
from ((select src, dest, amount
from transactions
where src < dest
) union all
(select dest, src, -amount
from transactions
where src > desc
)
) sd
group by src, desthttps://stackoverflow.com/questions/63527551
复制相似问题