更新时间:2023-11-26 21:57:40
应该这样做
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