thepirat000答案的变体:
-- 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中创建的,并根据需要为多列进行引用。
由于某些莫名其妙的原因,表变量现在被赋予了主键。如果有人可以真正解释一下它将如何提高性能,那么我很想听听它。从执行计划来看并不明显。