T-SQL(交叉?)联接 [英] T-SQL (Cross?) Joins
本文介绍了T-SQL(交叉?)联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
代码:
DECLARE @T1 TABLE (ID INT, Val1 VARCHAR(25), Val2 VARCHAR(25));
DECLARE @T2 TABLE (ID INT, Val3 BIT);
DECLARE @T3 TABLE (ID INT, Val4 DECIMAL(18,6));
INSERT INTO @T1 ( [ID], [Val1], [Val2] )
VALUES ( 1, 'V1One','V2One' )
,(2, 'V1Two','V2Two' )
,(3, 'V1Three','V2Three' )
,(4, 'V1Four','V2Four' );
INSERT INTO @T2 ( [ID], [Val3] )
VALUES ( 3, 1 );
INSERT INTO @T3 ( [ID], [Val4] )
VALUES ( 4, 9.99 )
,( 5, 0.99 );
期望的输出:
ID Val1 Val2 Val3 Val4
1 V1One V2One NULL NULL
2 V1Two V2Two NULL NULL
3 V1Three V2Three 1 NULL
4 V1Four V2Four NULL 9.990000
5 NULL NULL NULL 0.99
目标:
根据[ID]字段(键列")组合三个表并创建所需的输出.如果一条记录只存在于两个表中,则返回其他表的字段为 NULL - 即 ID = 3 和 4 如果记录存在于一个表中但不存在于其他表中,为其他表返回 NULL字段 - 即 ID = 1、2 和 5
To combine three tables based on [ID] field ("key column") and create the desired output. If a record exists in only two tables, return NULL for the other table's field(s) - i.e. ID = 3 and 4 If a record exists in one table but not other, return NULL for the other tables' field(s) - i.e. ID = 1, 2, and 5
查询必须快速执行,因为实际数据集非常庞大且复杂得多.
The query has to perform fast as the real data-set is quite huge and much more complex.
到目前为止我的尝试..
My try so far..
SELECT COALESCE([T1].[ID],[T2].[ID],[T3].[ID]), Val1, Val2, Val3, Val4
FROM @T1 T1
CROSS JOIN @T2 T2
CROSS JOIN @T3 T3
--WHERE T1.[ID] = T2.[ID]
--WHERE T1.[ID] = T3.[ID]
推荐答案
您想要 FULL join,而不是 CROSS join.
You want FULL join, rather than CROSS join.
SELECT COALESCE([T1].[ID],[T2].[ID],[T3].[ID]), Val1, Val2, Val3, Val4
FROM T1 T1
FULL JOIN @T2 T2 ON T2.ID = T1.ID
FULL JOIN @T3 T3 ON T3.ID = COALESCE(T1.ID, T2.ID)
这篇关于T-SQL(交叉?)联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文