一张表中的多列引用回一张表获取它们的值 [英] 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屋!