求高手写一条SQL查询语句！

jiangtao255 · 发表于 2017-3-28 20:04

有如下三张表
初期数

入库表

出库表

现在想通过一条SQL查询语句得到如下查询结果

ID  货号初期数  总入库数  总出库数  当前库存
1 A       10    400       110       300
2 B       20    300       200       120
3 C       30    50          0          80

请高手帮忙写个SQL语句，小弟感激不尽！！

amazeyeli · 发表于 2017-3-29 11:43

http://club.excelhome.net/forum. ... p;page=1#pid9036339

jack5d · 发表于 2017-3-30 16:17

SELECT
StarVal.ID,
StarVal.Model,
StarVal.Val,
IIf (
IsNull([intt].[Val]),
0,
[intt].[val]
) AS InTotal,
IIf (
IsNull([outtt].[Val]),
0,
[outtt].[val]
) AS OutTotal,
IIf (
IsNull([starVal].[val]),
0,
[StarVal].[Val]
) + IIf (
IsNull([intt].[val]),
0,
[intt].[Val]
) - IIf (
IsNull([outtt].[val]),
0,
[OutTT].[Val]
) AS SubTotal
FROM
(
SELECT
OutVal.Model,
SUM (OutVal.Val) AS Val
FROM
OutVal
GROUP BY
OutVal.Model
) AS OutTT
RIGHT JOIN (
(
SELECT
InVal.Model,
SUM (InVal.Val) AS Val
FROM
InVal
GROUP BY
InVal.Model
) AS InTT
RIGHT JOIN StarVal ON InTT.Model = StarVal.Model
) ON OutTT.Model = StarVal.Model;

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 求高手写一条SQL查询语句！

最新热点 /1