我怎么能做到这一点

Question

`

SELECT TXNID, 
     TOTAL_AMOUNT, 
     ACCOUNT_CATEGORY, 
     DEBIT 
FROM ACCOUNTING_BOOK 
    WHERE TXNID like 'M003%' ; 

TXNID：M003 TOTAL_AMT：25 ACCOUNT_CATEGORY：收入借：零贷：空

TXNID：M003 TOTAL_AMT：25 ACCOUNT_CATEGORY：资产借：零贷：空

填充借方列的规则：[Total-Amount if account-category = Expense或if account-category =相应的收入项目的资产，由TxnId匹配;否则为0]

想要验证此表内部的结果转换为借记栏。如何才能做到这一点？

信用列填充规则：[Total-Amount if account-category = Revenue或者account-category =由TxnId匹配的相应费用项目的资产将返回1;否则为0] 想要将此表内的结果验证为信用列。如何才能做到这一点？

Answer 1

如果我理解正确的规则：

update accounting_book ab 
    set debit = case when account_category in ('Expense', 'Asset') 
        then (select sum(total_amt) 
          from accounting_book 
          where account_category = 'Revenue' 
          and txnid = ab.txnid) 
        else 0 
       end 

---

编辑：

规则填充信用列：[总-金额如果 帐户类别=收入或者如果帐户类别=对应 的资产对应由TxnId匹配的费用项返回1; 0 否则]

到上一次更新类似：

update accounting_book ab 
    set credit = case when account_category in ('Revenue', 'Asset') 
        and exists (select 1 
            from accounting_book 
            where account_category = 'Expense' 
            and txnid = ab.txnid) 
        then 1 
        else 0 
       end 

Answer 2

 CASE WHEN A.ACCOUNTCATEGORY IN('Revenue','Asset') 
       AND EXISTS (SELECT TOTAL_AMOUNT 
       FROM ACCOUNTING_BOOK 
       WHERE ACCOUNTCATEGORY='Expense' 
       AND TXNID=A.TXNID) 
       THEN A.TOTAL_AMOUNT 
       ELSE 0 
       END, 

       CASE WHEN A.ACCOUNTCATEGORY IN('Expense','Asset') 
       AND EXISTS (SELECT TOTAL_AMOUNT 
       FROM ACCOUNTING_BOOK 
       WHERE ACCOUNTCATEGORY='Revenue' 
       AND TXNID=A.TXNID) 
       THEN A.TOTAL_AMOUNT 
       ELSE 0 
       end); 


END LOOP; 
END; 
/