时间戳

首页 > 解决方案 > POSTGRESQL：如何更新结果表中的列？

postgresql - POSTGRESQL：如何更新结果表中的列？

问题描述

为了基于与其他表的通用 ID 提取数据，我应用Select了查询以便我可以计算：贷方、借方和余额：

SELECT account_id as account_id,account_move_line.account_id as id, 
    COALESCE(SUM(debit),0) - COALESCE(SUM(credit), 0) as balance,
    COALESCE(SUM(debit), 0) as expense_income, 
    COALESCE(SUM(debit), 0) as debit, COALESCE(SUM(credit), 0) as credit 
FROM account_move as account_move_line__move_id,account_move_line
WHERE account_id IN (SELECT id FROM account_account WHERE user_type_id IN (SELECT id from account_account_type WHERE internal_group IN ('expense','income')))  
    AND ("account_move_line"."move_id"="account_move_line__move_id"."id") 
    AND (("account_move_line__move_id"."state" = 'posted')  ) 

GROUP BY account_id,account_move_line.account_id

我得到的结果是完美的：

在此处输入图像描述

我想要实现的是用结果表中没有 0 值的贷方和借方填充费用收入列。目前我只能通过使用我认为不是一种方式来填充第一个。我也关注了一些帖子来梳理这些逻辑，但我失败了。我也为糟糕的英语道歉。

我关注的帖子：

https://dba.stackexchange.com/questions/153174/postgresql-update-table-row-values-by-grouped-column

https://dba.stackexchange.com/questions/153174/postgresql-update-table-row-values-by-grouped-column

标签： postgresql

解决方案

如果我正确理解，您需要greatest借方和贷方之间的价值。如果是这样，那么你可以使用这个：

SELECT account_id as account_id,account_move_line.account_id as id, 
COALESCE(SUM(debit),0) - COALESCE(SUM(credit), 0) as balance,
greatest(COALESCE(SUM(debit), 0), COALESCE(SUM(credit), 0)) AS expense_income,


CASE WHEN COALESCE(SUM(debit),0) > COALESCE(SUM(credit), 0) THEN 'debit'
ELSE 'credit' END AS from_where_the_value_comming,

COALESCE(SUM(debit), 0) as debit, COALESCE(SUM(credit), 0) as credit 
FROM account_move as account_move_line__move_id,account_move_line
WHERE account_id IN (SELECT id FROM account_account WHERE user_type_id IN (SELECT id from account_account_type WHERE internal_group IN ('expense','income')))  
    AND ("account_move_line"."move_id"="account_move_line__move_id"."id") 
    AND (("account_move_line__move_id"."state" = 'posted')  ) 

GROUP BY account_id,account_move_line.account_id

推荐阅读