J
jackson_wu
Unregistered / Unconfirmed
GUEST, unregistred user!
我想实现这样的效果:<br>porder code name klotvalue bunr bumo kvol ndate fdate jitu0<br>A001 A AB ABC T5 MC736 1000 20060901 20060920 1200<br>A002 A AB ABC T5 MC736 1000 20060902 20060922 1500<br>合计 A 2000 2700<br>A009 C CB ABC T5 MC736 1000 20060901 20060920 1200<br>A010 C CB ABC T5 MC736 1000 20060901 20060920 1100<br>合计 C 2000 2300<br>我的SQL语句是这样写的:<br> with self.ADOQuery1 do<br> begin<br> self.ADOQuery1.Close;<br> self.ADOQuery1.SQL.Clear;<br> self.ADOQuery1.SQL.Add(<br> 'select a.porder,a.code,c.name,f.klotvalue,e.bunr,a.bumo,d.kvol,left(d.ndate,8) ndate,left(a.fdate,8) fdate,a.jitu0 from tpics31.dbo.xsact a '<br> +' left join (select porder,ocnt from sks.dbo.hs_proc where KBAN='+''''+'999'+''''+ ' and class='+''''+'j'+''''+') b on a.porder=b.porder '<br> +' left join tpics31.dbo.xhead c on a.code=c.code '<br> +' left join (select distinct code, bunr from tpics31.dbo.xitem) e on a.code=e.code '<br> +' left join tpics31.dbo.xslip d on a.porder=d.porder and a.code=d.code '<br> +' left join sks.dbo.hs_procl f on a.porder =f.kporder and f.klotcode='+''''+'9998'+''''<br> +' where a.porder in (select porder from sks.dbo.hs_proc where KBAN='+''''+'999'+''''+' and class='+''''+'j'+''''+')'<br> +' and a.code like :strcode and e.bunr like :strbunr and a.bumo like :strbumo and a.fdate like :strfdate '<br><br> +' union ' <br> +' select porder='+''''+'合计'+''''+' ,code,name='+''''+''''+',klotvalue='+''''+''''+',bunr='+''''+''''+',bumo='+''''+''''<br> +' ,sum(kvol) kvol,ndate='+''''+''''+',fdate='+''''+''''+',sum(jitu0) jitu0 from ('<br> +' select a.porder,a.code,c.name,f.klotvalue,e.bunr,a.bumo,d.kvol,left(d.ndate,8) ndate,left(a.fdate,8) fdate,a.jitu0 from tpics31.dbo.xsact a '<br> +' left join (select porder,ocnt from sks.dbo.hs_proc where KBAN='+''''+'999'+''''+ ' and class='+''''+'j'+''''+') b on a.porder=b.porder '<br> +' left join tpics31.dbo.xhead c on a.code=c.code '<br> +' left join (select distinct code, bunr from tpics31.dbo.xitem) e on a.code=e.code '<br> +' left join tpics31.dbo.xslip d on a.porder=d.porder and a.code=d.code '<br> +' left join sks.dbo.hs_procl f on a.porder =f.kporder and f.klotcode='+''''+'9998'+''''<br> +' where a.porder in (select porder from sks.dbo.hs_proc where KBAN='+''''+'999'+''''+' and class='+''''+'j'+''''+')'<br> +' and a.code like :strcode and e.bunr like :strbunr and a.bumo like :strbumo and a.fdate like :strfdate ) a '<br> +' group by a.code '<br> +' order by a.code ' );<br> self.ADOQuery1.Parameters.ParamByName('strcode').DataType:=ftString;<br> self.ADOQuery1.Parameters.ParamByName('strcode').Value:=Trim(self.Edit1.Text)+'%';<br> self.ADOQuery1.Parameters.ParamByName('strbunr').DataType:=ftString;<br> self.ADOQuery1.Parameters.ParamByName('strbunr').Value:=Trim(self.ComboBox2.Text)+'%';<br> self.ADOQuery1.Parameters.ParamByName('strbumo').DataType:=ftString;<br> self.ADOQuery1.Parameters.ParamByName('strbumo').Value:=Trim(self.ComboBox1.Text)+'%';<br> self.ADOQuery1.Parameters.ParamByName('strfdate').DataType:=ftString;<br> self.ADOQuery1.Parameters.ParamByName('strfdate').Value:=self.Edit2.Text+'%';<br> self.ADOQuery1.open;<br>union下面是生成合计行,程序在执行时报错:不正常地定义参数对象,提供了不一致或不完整的信息,如果我把union上面或下面单独执行,不报错,请高手指点心一下。