多个Select语句嵌套,据说最多是七层,没试过,这里是三层。
SELECT inck.ToCK as 仓库, chanpin.CPno AS 产品编号, chanpin.CPpinpai AS 产品品牌, chanpin.CPname AS 产品名称, chanpin.CPKind AS 产品规格, chanpin.CPunit as 计数单位, inck.INyouxiao as 有效期至, inck.INchangshang as 生产厂商, SUM(inck.INnum) AS 入库数量, IIF(AA.CC is not null,AA.CC,0) AS 出库数量, 入库数量-出库数量 as 库存数量
FROM (inck INNER JOIN chanpin ON inck.INcp=chanpin.CPno) LEFT JOIN
(SELECT outck.outcp AS BB, outck.OUTyouxiao as DD, outck.OUTchangshang as EE, Sum(outck.OUTnum) AS CC FROM outck
GROUP BY outck.outcp, outck.OUTyouxiao, outck.OUTchangshang, outck.FromCK HAVING(outck.FromCK='KJC-A')) AS AA
ON inck.INcp=AA.BB And inck.INyouxiao=AA.DD
GROUP BY chanpin.ID, chanpin.CPno, chanpin.CPpinpai, chanpin.CPname, chanpin.CPKind, inck.ToCK, chanpin.CPunit,
inck.INyouxiao, inck.INchangshang, AA.CC
HAVING (((inck.ToCK)='KJC-A'))
选择库存数量大于0的记录:
Select * From
(SELECT inck.ToCK as 仓库, chanpin.CPno AS 产品编号, chanpin.CPpinpai AS 产品品牌, chanpin.CPname AS 产品名称, chanpin.CPKind AS 产品规格, chanpin.CPunit as 计数单位, inck.INyouxiao as 有效期至, inck.INchangshang as 生产厂商, SUM(inck.INnum) AS 入库数量, IIF(AA.CC is not null,AA.CC,0) AS 出库数量, 入库数量-出库数量 as 库存数量
FROM (inck INNER JOIN chanpin ON inck.INcp=chanpin.CPno) LEFT JOIN
(SELECT outck.outcp AS BB, outck.OUTyouxiao as DD, outck.OUTchangshang as EE, Sum(outck.OUTnum) AS CC FROM outck
GROUP BY outck.outcp, outck.OUTyouxiao, outck.OUTchangshang, outck.FromCK HAVING(outck.FromCK='KJC-A')) AS AA
ON inck.INcp=AA.BB And inck.INyouxiao=AA.DD
GROUP BY chanpin.ID, chanpin.CPno, chanpin.CPpinpai, chanpin.CPname, chanpin.CPKind,
inck.ToCK, chanpin.CPunit, inck.INyouxiao, inck.INchangshang, AA.CC
HAVING (((inck.ToCK)='KJC-A'))) as AAA
where AAA.库存数量>0