問題描述

兩列的 case 條件相同.在下面的語句中，我使用了兩次但對(duì)于不同的列，有沒有其他方法可以不重復(fù)兩次條件??

The case condition for two columns is same.in the below statement am using this twice but for different column, is there any other way for not repeating the condition twice ??

case [CPHIL_AWD_CD]
                     when ' ' then 'Not Applicable/ Not a Doctoral Student'
                     when 'X' then 'Not Applicable/ Not a Doctoral Student'
                     when 'N' then 'NO'
                     when 'Y' then 'YES'
                end as CPHIL_AWD_CD

              ,case [FINL_ORAL_REQ_CD] 
                     when ' ' then 'Not Applicable/ Not a Doctoral Student'
                     when 'X' then 'Not Applicable/ Not a Doctoral Student'
                     when 'N' then 'NO'
                     when 'Y' then 'YES'
                end as FINL_ORAL_REQ_CD

推薦答案

thepirat000 答案的變體:

A variation on thepirat000's answer:

-- Sample data.
declare @Samples as Table (
  Frisbee Int Identity Primary Key, Code1 Char(1), Code2 Char(2) );
insert into @Samples values ( 'Y', 'N' ), ( ' ', 'Y' ), ( 'N', 'X' );
select * from @Samples;

-- Handle the lookup.
with Lookup as (
  select * from ( values
    ( ' ', 'Not Applicable/ Not a Doctoral Student' ),
    ( 'X', 'Not Applicable/ Not a Doctoral Student' ),
    ( 'N', 'No' ),
    ( 'Y', 'Yes' ) ) as TableName( Code, Description ) )
select S.Code1, L1.Description, S.Code2, L2.Description
    from @Samples as S inner join
      Lookup as L1 on L1.Code = S.Code1 inner join
      Lookup as L2 on L2.Code = S.Code2;

查找表是在 CTE 中創(chuàng)建的，并根據(jù)需要為多列引用.

The lookup table is created within a CTE and referenced as needed for multiple columns.

更新:由于某些莫名其妙的原因，表變量現(xiàn)在擁有主鍵.如果有人能真正解釋它如何有利于性能，我很想聽聽.從執(zhí)行計(jì)劃上看不明顯.

Update: The table variable is now blessed with a primary key for some inexplicable reason. If someone can actually explain how it will benefit performance, I'd love to hear it. It isn't obvious from the execution plan.

這篇關(guān)于我需要為每個(gè)字段重寫 case 語句嗎?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！