在具有不同名称的列上联接表，但在结果中产生单个列 [英] Joining tables on columns with different names but produce single column in result

问题描述

我不确定如何简洁地表达问题以描述我想解决的问题.
我有以下两个表:
Table 1

I'm not sure how to concisely formulate question to describe a problem I would like to solve.
I have two following tables:
Table 1

[idA] [numA]
 NULL   8
 1      10
 2      15
 3      16

Table 2

[idB] [numB]
 2      14
 3      30
 4      32

现在，我不确定如何制定T-Sql查询以产生以下结果:

Now, I'm not sure how to formulate T-Sql query to produce following result:

[id] [numA] [numB]
NULL  8      0
1     10     0
2     15     14
3     16     30
4     0      32

关于如何解决这个问题有什么建议吗?

Are there any suggestions on how to solve this?

更新:

如果要再加入一个表(idC，numC)，@ AdaTheDev的脚本是否会有任何问题?在那种情况下，最佳解决方案是什么?问题是我有15个要加入一个表，它们应该按id分组，并有15个对应的numX列.

Would there be any problems with @AdaTheDev's script if there was one more table (idC, numC) to join? In that case what would be the best solution? The thing is I have 15 of them to join into one table and they should be grouped by id and have 15 corresponding numX columns.

推荐答案

应该这样做

SELECT ISNULL(t1.idA, t2.idB) AS id, 
    ISNULL(t1.numA, 0) AS numA, 
    ISNULL(t2.NumB, 0) AS numB
FROM table1 t1
    FULL OUTER JOIN table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

更新
注意，我在联接中添加了OR条件，以处理idA和idB为NULL的情况，以给出单个结果

Update
Note I've added an OR condition to the join to handle the case where idA and idB are NULL, to give a single result

完整测试脚本(在表2中添加了NULL id记录):

Full test script (with added NULL id record in table2):

DECLARE @Table1 TABLE (ida integer, numA INTEGER)
DECLARE @Table2 TABLE (idb integer, numb INTEGER)

INSERT @Table1 ([ida], [numA])
VALUES (NULL, 8), (1, 10), (2, 15), (3, 16)

INSERT @Table2 ([idb], [numb])
VALUES (NULL, 9), (2, 14), (3, 30), (4, 32)

SELECT ISNULL(t1.idA, t2.idB) AS id, 
    ISNULL(t1.numA, 0) AS numA, 
    ISNULL(t2.NumB, 0) AS numB
FROM @table1 t1
    FULL OUTER JOIN @table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

这篇关于在具有不同名称的列上联接表，但在结果中产生单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！