根据条件更改包含多个字段的select的结果(SQL)

Question

我有这样的脚本:

SELECT TRANSACTION_CODE, LINK_NUMBER, INTERNAL_REFERENCE, PORTFOLIO_CODE, FILE_CODE, CMP_CODE, COUNTERPARTY_CODE, TRX_AMOUNT, CONVERT(varchar, BEGINNING_DATE, 3) AS 'BEGINNING_DATE', CONVERT(varchar, BOOK_DATE, 3) AS 'BOOK_DATE', 
CONVERT(varchar, END_DATE, 3) AS 'END_DATE', AMORT_FREQUENCY, END_OF_MONTH, NUMBER_OF_AMORT_PERIODS, CONVERT(varchar, FIRST_REDEMPTION_DATE, 3) AS 'FIRST_REDEMPTION_DATE', AMORT_PREPAID_FLAG, AMORT_CALC_MODE, 
AJUST_FIRST_AMORT_FLAG, AMORT_CALC_RATE, AMORT_RATE_INCREMENT, AMORT_FIRST_ANNUITY, AMORT_FIRST_REDEMPTION, INTEREST_FREQUENCY, CONVERT(varchar, FIRST_INT_DATE, 3) AS 'FIRST_INT_DATE', NUMBER_OF_INT_PERIODS, INT_PREPAID_FLAG, 
INT_CALC_BASIS, INT_RATE_TYPE, INT_FIXED_RATE, INT_MARKUP, INT_FLOOR, INT_CEILING, REFERENCE, PAYMENTS_CMP_ACC, AMORT_CMP_ACC, INT_CMP_ACC, FEE_CMP_ACC, NOTE_1, ADDIT_PAYMENT_CMP_ACCOUNT, ZU_01, ZU_02, ZU_03, ZU_04, ZU_05, ZU_06, 
ZU_07, ZU_08, ZU_09, ZU_10
FROM   LOANS
WHERE (LAST_VERSION_FLAG = - 1) AND (NUMBER_OF_PAYMENTS = 1) AND (PERIOD_DEFERRED_FLAG = 0) AND (TRX_CATEGORY = 75) AND (AMORT_CALC_MODE IN (0, 2, 3))

我想添加一个条件，使字段AMORT_FREQUENCY的行为如下:

当0时，则3当1和2当2然后1其他0

我试过CASE，但我不知道如何使它工作。如果这是一个非常基本的问题，我很抱歉。

提前感谢。

马里奥。

(添加)

字段AMORT_FREQUENCY可以包含以下值:0,1,2,3我想改变这个字段的输出值:

当该字段包含0时，更改为3当该字段包含1时，将其更改为2当该字段包含2时，更改为1当字段包含3时，更改为4

在这个选择中，我正在提取一个巨大的表，我需要改变这些值。

Answer 1

可以这样使用CASE:

select
AMORT_FREQUENCY,
case AMORT_FREQUENCY
when 0 then 3
when 1 then 2
when 2 then 3
when 3 then 4
end as amort_new
from test

参见:https://dbfiddle.uk/ITjjwjTx

然后您可以通过替换AMORT_FREQUENCY将CASE部分放入查询中，类似于(添加括号以提高可读性):

SELECT TRANSACTION_CODE, LINK_NUMBER, INTERNAL_REFERENCE, PORTFOLIO_CODE, FILE_CODE, CMP_CODE, COUNTERPARTY_CODE, TRX_AMOUNT, CONVERT(varchar, BEGINNING_DATE, 3) AS 'BEGINNING_DATE', CONVERT(varchar, BOOK_DATE, 3) AS 'BOOK_DATE', 
CONVERT(varchar, END_DATE, 3) AS 'END_DATE', (case AMORT_FREQUENCY when 0 then 3 when 1 then 2 when 2 then 3 when 3 then 4 end), END_OF_MONTH, NUMBER_OF_AMORT_PERIODS, CONVERT(varchar, FIRST_REDEMPTION_DATE, 3) AS 'FIRST_REDEMPTION_DATE', AMORT_PREPAID_FLAG, AMORT_CALC_MODE, 
AJUST_FIRST_AMORT_FLAG, AMORT_CALC_RATE, AMORT_RATE_INCREMENT, AMORT_FIRST_ANNUITY, AMORT_FIRST_REDEMPTION, INTEREST_FREQUENCY, CONVERT(varchar, FIRST_INT_DATE, 3) AS 'FIRST_INT_DATE', NUMBER_OF_INT_PERIODS, INT_PREPAID_FLAG, 
INT_CALC_BASIS, INT_RATE_TYPE, INT_FIXED_RATE, INT_MARKUP, INT_FLOOR, INT_CEILING, REFERENCE, PAYMENTS_CMP_ACC, AMORT_CMP_ACC, INT_CMP_ACC, FEE_CMP_ACC, NOTE_1, ADDIT_PAYMENT_CMP_ACCOUNT, ZU_01, ZU_02, ZU_03, ZU_04, ZU_05, ZU_06, 
ZU_07, ZU_08, ZU_09, ZU_10
FROM   LOANS
WHERE (LAST_VERSION_FLAG = - 1) AND (NUMBER_OF_PAYMENTS = 1) AND (PERIOD_DEFERRED_FLAG = 0) AND (TRX_CATEGORY = 75) AND (AMORT_CALC_MODE IN (0, 2, 3))