sql-server - SQL Server 加入查找表

Question

您好我有一个表 Meter 和一个查找表 LookupTbl 我需要通过加入查找表来获取多个字段的描述。

任何人都可以提出一个很好的方法来做到这一点，而无需多个连接，如下所示：

   select t.*, s.Description as SubTypeCD_DESC, d1.Description as 
AOD_METHOD_DESC, d2.Description as OWNER_DESC, d3.Description as 
ID_DMA_CODE_DESC, 
d4.Description as ID_CONTROL_GROUP_DESC, d5.Description as 
OPERATIONAL_STATUS_DESC, d6.Description as SOURCE_METHOD_DESC, d7.Description 
as QA_STATUS_DESC,
 d8.Description as SECURITY_IND_DESC, d9.Description as Enabled_DESC, 
d10.Description as AncillaryRole_DESC from CGIdb.CGI1.Meter t 

left join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) and 
upper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')
left join CGIdb.CGI1.[LookUpTbl2] d1 ON upper(t.AOD_METHOD)=upper(d1.Code) 
and upper(d1.Field) =upper('AOD_METHOD')
left join CGIdb.CGI1.[LookUpTbl2] d2 ON upper(t.OWNER)=upper(d2.Code) and 
 upper(d2.Field) =upper('OWNER')
left join CGIdb.CGI1.[LookUpTbl2] d3 ON upper(t.ID_DMA_CODE)=upper(d3.Code) 
and upper(d3.Field) =upper('ID_DMA_CODE')
left join CGIdb.CGI1.[LookUpTbl2] d4 ON 
upper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field) 
=upper('ID_CONTROL_GROUP')
left join CGIdb.CGI1.[LookUpTbl2] d5 ON 
upper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field) 
=upper('OPERATIONAL_STATUS')
left join CGIdb.CGI1.[LookUpTbl2] d6 ON upper(t.SOURCE_METHOD)=upper(d6.Code) 
and upper(d6.Field) =upper('SOURCE_METHOD')
left join CGIdb.CGI1.[LookUpTbl2] d7 ON upper(t.QA_STATUS)=upper(d7.Code) and 
upper(d7.Field) =upper('QA_STATUS')
left join CGIdb.CGI1.[LookUpTbl2] d8 ON upper(t.SECURITY_IND)=upper(d8.Code) 
and upper(d8.Field) =upper('SECURITY_IND')
left join CGIdb.CGI1.[LookUpTbl2] d9 ON upper(t.Enabled)=upper(d9.Code) and 
upper(d9.Field) =upper('Enabled')
left join CGIdb.CGI1.[LookUpTbl2] d10 ON 
upper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field) 
=upper('AncillaryRole')

标签： sql-server