Id num ProName
1 001 产品1
2 002 产品2
3 003 产品3
4 004 产品4
5 005 产品5
6 006 产品6
入库
Id num AddQuantity增加 ....
1 001 11 ....
2 002 12 ....
3 004 16 ....销售
Id num MinusQuantity增加 ....
1 001 22 ....
2 002 35 ....
3 005 56 ....
现在要做一个库存统计,查询的结果应该如下所示:
Num ProName AddQuantity MinusQuantity
001 产品1 11 22
002 产品2 12 35
004 产品4 16 0
005 产品5 0 56
1 001 产品1
2 002 产品2
3 003 产品3
4 004 产品4
5 005 产品5
6 006 产品6
入库
Id num AddQuantity增加 ....
1 001 11 ....
2 002 12 ....
3 004 16 ....销售
Id num MinusQuantity增加 ....
1 001 22 ....
2 002 35 ....
3 005 56 ....
现在要做一个库存统计,查询的结果应该如下所示:
Num ProName AddQuantity MinusQuantity
001 产品1 11 22
002 产品2 12 35
004 产品4 16 0
005 产品5 0 56
A.ProName ,
B.addquantity ,
b.MinusQuantity
FROM dbo.TB A
INNER JOIN ( SELECT ISNULL(a.num, b.num) AS num ,
ISNULL(A.addquantity, 0) AS addquantity ,
ISNULL(B.MinusQuantity, 0) AS MinusQuantity
FROM [入库] A
FULL JOIN [销售] B ON A.num = B.num
) B ON A.num = b.num/*
num ProName addquantity MinusQuantity
001 产品1 11 22
002 产品2 12 35
004 产品4 16 0
005 产品5 0 56*/
FROM '基础表' AS B
INNER JOIN 入库 AS A
ON B.Num = A.Num
INNER JOIN 销售 AS M
ON B.Num = M.Num