如何将两个sql表中的行和列合并到一个表中

I searched for a while but I couldn't find something similar.
I have two tables with data and I want to merge those two in two one.

我搜索了一会儿,但找不到类似的东西。我有两个数据表,我想将这两个合并为两个。

Tbl1
id nr val1
1  a1  123
2  a2  124
3  a3  125

Tbl2
id nr val2
5  a1  223
6  a2  224
7  a4  225

Resulting table should be something like this.

结果表应该是这样的。

Tbl
nr val1 val2
a1  123  223
a2  124  224
a3  125    0
a4    0  225

Any help would be appreciated. Thanks in advance.

任何帮助,将不胜感激。提前致谢。

It is MS SQL and I tried union and join. But they don't do.

它是MS SQL,我尝试了联合和加入。但他们不这样做。

3 个解决方案

#1

You can do it by doing a FULL OUTER JOIN and COALESCE the columns together.

您可以通过执行FULL OUTER JOIN并将列COALESCE来实现。

SELECT COALESCE(tbl1.nr,tbl2.nr) AS nr,tbl1.val1,tbl2.val2 
FROM tbl1 FULL OUTER JOIN tbl2 
ON tbl1.nr = tbl2.nr;

#2

if you use MS SQL then you could try full outer join and Isnull function to get the result you need.

如果您使用MS SQL,那么您可以尝试使用完全外连接和Isnull函数来获得所需的结果。

SELECT Isnull(tbl1.nr, tbl2.nr) nr, 
    Isnull(tbl1.val1, 0) val1,
      Isnull(Tbl2.val2, 0) val2 FROM tbl1 FULL OUTER JOIN tbl2 ON tbl1.nr = tbl2.nr

#3

SELECT NVL(a1.nr,b1.nr) as nr,
       NVL(a1.val1,0) AS val1,
       NVL(b1.val2,0) AS val2
FROM table1 a1
     FULL OUTER JOIN table2 b1 ON a1.nr = b1.nr
ORDER BY a1.nr;

#1