一个表中引用回一个表的多个列将获取它们的值

您是否尝试过对table2表进行别名处理,并将其两次连接到table_One,如下所示?

SELECT
    t1.PrimaryKey,
    c1.ColumnA AS Column1,
    c2.ColumnA AS Column2
FROM Table_One t1
JOIN Table_two c1 ON t1.Column1 = c1.ID
JOIN Table_two c2 ON t1.Column2 = c2.ID;

以下解决方案( SQLFiddle )只读取第二个表中的行一次:

SET STATISTICS IO ON;
...
PRINT 'Test #1'
SELECT  *
FROM
(
    SELECT  ca.PrimaryKey, ca.[Type], y.ColumnA
    FROM    @Table1 x
    UNPIVOT( Value FOR [Type] IN ([Column1], [Column2]) ) ca
    INNER MERGE /*HASH*/ JOIN @Table2 y ON ca.Value = y.ID
) src
PIVOT( MAX(src.ColumnA) FOR src.[Type] IN ([Column1], [Column2]) ) pvt
PRINT 'End of Test #1'

结果:

Test #1
PrimaryKey Column1   Column2
---------- --------- -------
1          ALPHA     CHARLIE
2          BETA      DELTA
3          CHARLIE   ALPHA
4          DELTA     CHARLIE
5          ALPHA     DELTA
6          CHARLIE   ALPHA
7          ALPHA     DELTA
8          DELTA     CHARLIE

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#65B6F546'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#61E66462'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

End of Test #1

Select Table_One.PrimaryKey,
       T2_Column1.ColumnA As Column1,
       T2_Column2.ColumnA As Column2
From   Table_One
       Inner Join Table_Two As T2_Column1
         On Table_One.Column1 = T2_Column1.ID
       Inner Join Table_Two As T2_Column2
         On Table_One.Column2 = T2_Column2.Id

基本上,你加入了两次表2。当您这样做时,您必须给其中至少一个别名,这样SQL就不会混淆。作为一种实践,通常最好将两者都别名,这样当您在6个月后再次阅读此代码时,它将更容易理解。