时间戳

首页 > 解决方案 > 如何从列中分离值

sql - 如何从列中分离值

问题描述

列填充借方和贷方值，如

100
150
-300
400

-300我为as创建了新列CREDIT_ACCOUNT。由于该列中填充了值。该值应null在DEBITCOLUMN中设置为

整个数据是基于Select和Case语句提取和填充的。如何使 sql WORK 以NULL在-300COLUMNDEBIT中设置

COALESCE
        (
        CASE DBO.TRUNC_NUM1((DBO.TO_NUMBER(DBO.TO_DATE(#prompt('Q_Date','string')#,'yyyy-mm-dd')-AR_ACCOUNT_ITEM_NEW.BILL_DATE))/#prompt('P_Interval')#)
           WHEN 0 THEN 
                       CASE RTRIM(AR_ACCOUNT_ITEM_NEW.BILL_REF)
                          WHEN 'BILL' THEN AR_ACCOUNT_ITEM_NEW.REMAINING_AMOUNT
                       END
           ELSE NULL
        END 
        ,0) THIS_MONTH,       
        COALESCE
        (
        CASE DBO.TRUNC_NUM1((DBO.TO_NUMBER(DBO.TO_DATE(#prompt('Q_Date','string')#,'yyyy-mm-dd')-AR_ACCOUNT_ITEM_NEW.BILL_DATE))/#prompt('P_Interval')#)
           WHEN 1 THEN 
                       CASE RTRIM(AR_ACCOUNT_ITEM_NEW.BILL_REF)
                          WHEN 'BILL' THEN AR_ACCOUNT_ITEM_NEW.REMAINING_AMOUNT
                       END
           ELSE NULL
        END 
        ,0) THIRTY,       
        COALESCE
        (
        CASE DBO.TRUNC_NUM1((DBO.TO_NUMBER(DBO.TO_DATE(#prompt('Q_Date','string')#,'yyyy-mm-dd')-AR_ACCOUNT_ITEM_NEW.BILL_DATE))/#prompt('P_Interval')#)
           WHEN 2 THEN 
                       CASE RTRIM(AR_ACCOUNT_ITEM_NEW.BILL_REF)
                          WHEN 'BILL' THEN AR_ACCOUNT_ITEM_NEW.REMAINING_AMOUNT
                       END
           ELSE NULL
        END 
        ,0) SIXTY,
        COALESCE
        (
        CASE DBO.TRUNC_NUM1((DBO.TO_NUMBER(DBO.TO_DATE(#prompt('Q_Date','string')#,'yyyy-mm-dd')-AR_ACCOUNT_ITEM_NEW.BILL_DATE))/#prompt('P_Interval')#)
           WHEN 3 THEN 
                       CASE RTRIM(AR_ACCOUNT_ITEM_NEW.BILL_REF)
                          WHEN 'BILL' THEN AR_ACCOUNT_ITEM_NEW.REMAINING_AMOUNT
                       END
           ELSE NULL
        END 
        ,0) NINETY,      
        DBO.TO_NUMBER
        (
        COALESCE
        (
        CASE DBO.TRUNC_NUM1((DBO.TO_NUMBER(DBO.TO_DATE(#prompt('Q_Date','string')#,'yyyy-mm-dd')-AR_ACCOUNT_ITEM_NEW.BILL_DATE))/#prompt('P_Interval')#)
           WHEN 0 THEN NULL
           WHEN 1 THEN NULL
           WHEN 2 THEN NULL
           WHEN 3 THEN NULL
           ELSE 
                CASE RTRIM(AR_ACCOUNT_ITEM_NEW.BILL_REF)
                   WHEN 'BILL' THEN AR_ACCOUNT_ITEM_NEW.REMAINING_AMOUNT
                END
        END 
        ,0)
        ) ONETWENTY,

标签： sqlsql-server

解决方案

如果您只想将负值放在不同的列中，方法如下：

Select Case When Debit_Amount >= 0 then Debit_Amount Else Null End as Debit_Amount,
    Case When Debit_Amount < 0 then Debit_Amount Else Null End as Credit_Amount, 
<Other Columns>
From
(
    -- Your Query to get Debit_Amount and other data
) Q

-- 或者如果您的表已经被填充：

Update YourTable 
Set Credit_Amount = Debit_Amount
Where Debit_Amount < 0

Update YourTable 
Set Debit_Amount = Null
Where Debit_Amount < 0

推荐阅读