选择在两个不同的表上连接了两个不同条件的列

Question

对于下面的代码，我需要从表#ScoreTemp中选择列[Score Value]，但我需要根据另一列[Score]等于一列的字符串'Fico'和另一列的'Revbal'的条件将其选择为两个不同的列。当两个条件的值都存在于一个表中时，我不确定如何做到这一点。如有任何帮助，我们将不胜感激。谢谢

SELECT a.CBRRequestDate AS UpdateDate
,b.[Score Value] AS Fico
,c.[Score Value] AS Revbal
,NULL AS TUGroup
,a.Vision_Practitioner_Id__c AS PID
FROM [SFOnPrem].[dbo].[Contact] a
LEFT JOIN #ScoreTemp d ON a.Id = d.Guarantor__c
LEFT JOIN #ScoreTemp b ON a.Id = b.Guarantor__c
AND b.Score = 'Fico'
LEFT JOIN #ScoreTemp c ON a.Id = c.Guarantor__c
AND c.Score = 'Revbal'

Answer 1

您的代码应该很好。但是，如果您缺少值，则使用LEFT JOIN:

SELECT c.CBRRequestDate AS UpdateDate,
stf.[Score Value] AS Fico,
str.[Score Value] AS Revbal,
NULL AS TUGroup,
c.Vision_Practitioner_Id__c AS PID
FROM [SFOnPrem].[dbo].[Contact] c LEFT JOIN
#ScoreTemp stf
ON a.Id = stf.Guarantor__c AND
stf.Score = 'Fico' LEFT JOIN
#ScoreTemp str
ON a.Id = str.Guarantor__c AND
str.Score = 'Revbal';

请注意，我修复了表别名，使它们不再是毫无意义的字母，而是表名的缩写。我还更改了第一列，使其来自contact——在本文中，这似乎是合乎逻辑的，但可能不正确。