求上月结存和本月结余的查询语句 - 编程论坛

srxj2011-07-17 22:38

没有人帮我吗？

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

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';