本文介紹了我需要為每個字段重寫 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.
這篇關于我需要為每個字段重寫 case 語句嗎?的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!