我需要为每个字段重写 case 语句吗?

问题描述

两列的 case 条件相同.在下面的语句中，我使用了两次但对于不同的列，有没有其他方法可以不重复两次条件??

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 中创建的，并根据需要为多列引用.

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

更新:由于某些莫名其妙的原因，表变量现在拥有主键.如果有人能真正解释它如何有利于性能，我很想听听.从执行计划上看不明显.

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.