关于Full join 连接查询
两张表如下:
表A 表B
a b c a b c
100 x 32 100 x 3
100 y 31 100 b 12
100 z 42 100 n 32
201 y 43 201 w 10
201 q 23 333 x 43
语句如下:
SELECT A.IceNo, A.ProductId, ISNULL(A.c, 0) - ISNULL(B.c, 0)
AS c
FROM A FULL OUTER JOIN
B ON A.a = B.a AND A.b = B.b
得到如下结果:
a b c
100 x 29
100 y 31
100 z 42
NUll NUll -12
NUll NUll -32
201 y 43
201 q 23
NUll NUll -10
NUll NUll -43
而我想要得到的结果是:
a b c
100 x 29
100 y 31
100 z 42
100 b -12
100 n -32
201 y 43
201 q 23
201 w -10
201 x -43
请各位指教,谢谢!
[解决办法]
不是用JOIN，是用UNION吧：
select * from A union all
select * from B
[解决办法]
SELECT IsNull(A.IceNo, B.IceNo) As IceNo, IsNull(A.ProductId, B.ProductId) As ProductId, ISNULL(A.c, 0) - ISNULL(B.c, 0)
AS c
FROM A FULL OUTER JOIN
B ON A.a = B.a AND A.b = B.b
