主页 / dba / 问题 / 233644
Accepted
kn3l
Asked: 2019-04-02 00:17:41 +0800 CST

选择一列进入借方和贷方

  • 772
SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

如果 > 0 贷方或借方，我如何将“金额”拆分为 2 列debit并credit基于条件。

sql-server

1 个回答

  1. Best Answer
    declare @t table (LEAD_CO_MNE int identity, CURRENCY varchar(10), AMOUNT_FCY int, AMOUNT_LCY int);
insert into @t (CURRENCY, AMOUNT_FCY, AMOUNT_LCY)
values ('KHR', 10, 20), ('KHR', -10, -20), ('KHR', 30, 40), ('other', 1, 2);

with cte as
(
SELECT [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END Amount --I want to split into type of amount debit or crdit
     ,CURRENCY
from @t
)

select [LEAD_CO_MNE], 
       CURRENCY,
       case when Amount > 0 then Amount else 0 end as Credit,
       case when Amount < 0 then Amount else 0 end as Debit
from cte;
    • 1

相关问题