sql - 在sql中满足条件时对变量求和

Question

我有一个表 mes_transaction，其中有变量 dc - 它是 DEBIT 或 CREDIT。我正在为另一个表编写一个 sql 过程(如果我可以这样说的话)。在另一个表 mes_aggregated_trx 中，我有 trx_amt - 交易金额变量。基本上我需要做的是编写一个程序，根据 mes_transaction 中的 dc 对 mes_aggregated_trx 中的 trx_amt 求和.

它需要看起来像这样(伪代码):

IF dc = 'CREDIT' THEN ADDTOSUM(trx_amt) ELSE SUBFROMSUM(trx_amt)

我不知道如何在 sql 中实现 IF 子句。或者也许我应该做一些事情，比如对 CREDIT trx_amt 求和，然后对 DEBIT trx_amt 求和，然后减去总和以获得期望的结果？但问题是程序看起来像这样:

$BODY$
BEGIN
    INSERT INTO mes_aggregated_trx(pos_id
                                  ,mes_account_id
                                  ,merchant_code
                                  ,trx_count
                                  ,fee_amount
                                  ,trx_amount
                                  ,date)
    SELECT pos_id
          ,mes_account_id
          ,merchant_code as location_id
          ,count(1) as count
          ,sum(comm_amt) as fee_amount
          ,sum(trx_amt) as trx_amount
          ,aggregation_date 
    FROM mes_transaction
    WHERE post_date =aggregation_date
    AND bic = aggregation_bic
    AND rec_type='TRX'
    AND (status='OK' OR status='OVR')
    GROUP BY pos_id, merchant_code, mes_account_id
    ON CONFLICT (date, merchant_code, pos_id, mes_account_id)
    DO UPDATE
       SET trx_count = excluded.trx_count
          ,trx_amount = excluded.trx_amount
          ,fee_amount = excluded.fee_amount;

    -- Conflicts shouldn't happen since before insert new trx for date everything
    -- for that date is cleaned, but to be 100% sure not to duplicate date, newest
    -- date is stored.
RETURN 1;  
END;
$BODY$

所以我需要以某种方式重做程序的整个结构，而我真的不知道该怎么做。所以任何帮助、提示或建议都会很棒!谢谢

Answer 1

IF dc = 'CREDIT' THEN ADDTOSUM(trx_amt) ELSE SUBFROMSUM(trx_amt)

在 SQL 中会像这样:

SUM(CASE WHEN dc='CREDIT' THEN trx_amt ELSE -(trx_amt) END)