SQL SERVER LEFT JOIN 多表问题。谢谢。(50)

SELECT c.供货商名称, c.商品编码, c.商品名称, c.进货总数量, c.销售总数量, IIf(IsNull(f.退货总数量),0,f.退货总数量) AS 退货总数量, IIf(IsNull(d.报废总数量),0,d.报废总数量) AS 报废总数量, (c.现有库存-IIf(IsNull(d.报废总数量),0,d.报废总数量) -IIf(IsNull(f.退货总数量),0,f.退货总数量) ) AS 现有库存, c.商品类型, SWITCH((现有库存<0),"【出错】现有库存是负数",d.报废总数量>0,"报废过",True,"正常") AS 备注FROM [SELECT a.*, IIf(IsNull(b.销售总数量),0,b.销售总数量) AS 销售总数量, (a.进货总数量-IIf(IsNull(b.销售总数量),0,b.销售总数量) ) AS 现有库存 FROM (SELECT 供货商名称,商品编码, 商品名称, 商品类型, sum(数量) AS 进货总数量 FROM 进货报表20100401 GROUP BY 供货商名称,商品编码, 商品名称, 商品类型) AS a LEFT JOIN (SELECT 商品编码, 商品名称, 商品类型, sum(数量) AS 销售总数量 FROM 销售报表20100401 GROUP BY 商品编码, 商品名称, 商品类型) AS b ON a.商品编码=b.商品编码]. AS c LEFT JOIN [SELECT 商品编码, 商品名称, 商品类型, sum(数量) AS 报废总数量 FROM 报废登记20100401 GROUP BY 商品编码, 商品名称, 商品类型]. AS d ON c.商品编码=d.商品编码 ---在这里要再连接一个表,如下，但是出错了。请高手帮忙改一下。谢谢了。----------------------------- ---LEFT JOIN (select 商品编码,sum(数量) as 退货总数量 from 客户退货20100401 GROUP BY 商品编码) as f ----------------------------------------- WHERE 现有库存 < 5ORDER BY c.商品类型, -c.销售总数量, c.商品名称;

IIf(IsNull(f.退货总数量),0,f.退货总数量)这句就有误吧？IsNull(f.退货总数量)返回的不是布尔型吧？出错也不说出是什么错误“---LEFT JOIN (select 商品编码,sum(数量) as 退货总数量 from 客户退货20100401 GROUP BY 商品编码) as f”后面怎么没有On？

sql server 有iif ?我记得只有Access中才有啊.

用 case when 代替, iif 只有 access 才有的.