三张表
采购表
出库表
销售表

 

表的结构是一样的

商品名称(spmc)，商品编码(spbm)，单位(dw)，数量(number)，单价(price)，采购日期(cgrq)

...................................................................... 出库日期(ckrq)

....................................................................... 销售日期(xsrq)

我的目的是要按照自定义的时间段(StarDate,EndDate)查出如下统计表

商品名称，商品编码，单位，单价，上月结存数，上月结存金额，本月进货数，本月进货金额，本月出货数，本月出货金额，本月销售数，本月销售金额，本月结余数，本月结余金额

其中单价为采购表中的单价，出货金额，销售金额都为单价乘以数量所得

大家教我下，这个复合查询语句应该怎么写啊，我怎么写都通不过，纠结啊

各位老师们救救我吧。。。。

没有人帮我吗？

以下是我自己写的语句，太过于复杂了，而且也通不过

 kccx:='SELECT cgmx.spbm as spbm,cgmx.spmc as spmc,cgmx.jldw as dw,cgmx.price as price,'
  +' (select (select sum(cgmx.number) where cgrq<Datetimepicker1)
  +' -IIf(IsNull(select sum(spxs.number) from spxs where xsrq<DateTimepicker1),0,sum(spxs.number)
  +'-IIf(IsNull(select sum(spck.number) from spck where ckrq<DateTimepicker1),0,sum(spck.number))'
  +' FROM cgmx LEFT JOIN spxs ON cgmx.spbm=spxs.spbm
  + left join spck on cgmx.spbm=spck.spbm) AS jcsl'
  +' price*jcsl as jcje'
  +' (select spbm,sum(number) from cgmx group by spbm) as jhsl'
  +' price*jhsl as jhje'
  +' (select spbm,sum(number) from spck group by spbm) as chsl'
  +' price*chsl as chje'
  +' (select spbm,sum(number) from spxs group by spbm) as xssl'
  +' price*xssl as xsje'
  +' (jcsl+jhsl-chsl-xssl) as jysl'
  +' price*jysl as jyje'
  +' from cgmx left join spck on cgmx.spbm=spck.spbm'
  +' left join spxs on cgmx.spbm=spxs.spbm'
  +' where between #'+formatdatetime('yyyy-mm-dd',DateTimePicker1.datetime)+'# and #'+formatdatetime('yyyy-mm-dd',DateTimePicker2.datetime)+'#'
  +' group by cgmx.spmc,cgmx.spbm,cgmx.jldw';