计算库存量:
SELECT
TOP
100
PERCENT
dbo.Office_In.InCode, dbo.Office_In.InDate,
dbo.Office_In.ComCode, dbo.Office_In.TypeCode, dbo.Office_In.OName,
dbo.Office_In.Spec, dbo.Office_In.Unit, dbo.Office_In.Qty, dbo.Office_In.Price,
dbo.Office_In.Provider, dbo.Office_In.Master, dbo.Office_In.Note,
dbo.Company.ComName, dbo.Office_Type.TypeName, dbo.Office_In.Qty
-
(
SELECT
isnull
(
SUM
(Office_Out.Qty),
0
)
FROM
Office_Out
WHERE
Office_In.InCode
=
Office_Out.InCode)
AS
Stock,
(
SELECT
isnull
(
SUM
(office_out.qty),
0
)
FROM
office_out
WHERE
office_in.inCode
=
office_out.incode)
AS
outQty
FROM
dbo.Office_Type
INNER
JOIN
dbo.Office_In
ON
dbo.Office_Type.TypeCode
=
dbo.Office_In.TypeCode
LEFT
OUTER
JOIN
dbo.Company
ON
dbo.Office_In.ComCode
=
dbo.Company.ComCode
ORDER
BY
dbo.Company.ComName, dbo.Office_Type.TypeName
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
在这个SQL语句,主要是利用isnull函数,将从数据库中查询到的为null的值,转化为0,之后利用减法,将库存计算出来。
附:结构表