一张表中的多列引用回一张表获取它们的值 [英] Multiple Columns in one table referencing back to one table get their values

问题描述

我有一个表 table_One 它有多个 columns(Column1, Column2, Column3, Column4.....) 两个引用(包含 PK 值Table_two) 到另一个表 Table_Two.有没有什么有效的方法可以加入这两个表，而不是将 Table_one 多次加入 table_Two.br/>

I have a table table_One which has Multiple columns(Column1, Column2, Column3, Column4.....) both references (Contains PK values for Table_two) to another table Table_Two. Is there any efficient way getting joining these two tables rather than joining Table_one back to table_Two Multiple Times.br/>

两个表的结构和期望的结果集如下.表_一

The Structure of the Two tables and the Desired Result Set is as follows. Table_One

推荐答案

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

Following solution (SQLFiddle) reads the rows from the second table just one time:

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

这篇关于一张表中的多列引用回一张表获取它们的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！