我要查询各种产品的库存量,SELECT语句这样写:
select 产品编号, 产品名称,
((SELECT SUM(进货数量) from 进货记录 AS b WHERE a.产品编号=b.产品编号) - (SELECT SUM(购买数量) from 销售记录 AS c WHERE a.产品编号=c.产品编号))
AS 库存量 from 产品目录 AS a
问题是:当进货数量和购买数量都有该产品编号的记录时,库存量的计算是正确的,但当该产品编号的产品没有销售量(购买数量)时,查询出来的库存量为空值。我该怎样改写这条查询语句呢?
select 产品编号, 产品名称,
((SELECT SUM(进货数量) from 进货记录 AS b WHERE a.产品编号=b.产品编号) - (SELECT SUM(购买数量) from 销售记录 AS c WHERE a.产品编号=c.产品编号))
AS 库存量 from 产品目录 AS a
问题是:当进货数量和购买数量都有该产品编号的记录时,库存量的计算是正确的,但当该产品编号的产品没有销售量(购买数量)时,查询出来的库存量为空值。我该怎样改写这条查询语句呢?
((SELECT ISNULL(SUM(进货数量),0) from 进货记录 AS b WHERE a.产品编号=b.产品编号) - (SELECT ISNULL(SUM(购买数量),0) from 销售记录 AS c WHERE a.产品编号=c.产品编号))
AS 库存量 from 产品目录 AS a
用于函数参数的个数不对 在查询表达式 'ISNULL(SUM(进货数量), 0)' 中。.
---------------------------
((SELECT iif(SUM(进货数量)=null,0,SUM(进货数量)) from 进货记录 AS b WHERE a.产品编号=b.产品编号) - (SELECT iif(SUM(购买数量)=null,0,SUM(购买数量)) from 销售记录 AS c WHERE a.产品编号=c.产品编号))
AS 库存量 from 产品目录 AS a
SELECT iif(ISNULL(SUM(进货数量)),0,SUM(进货数量))……
才可以了。