第一条复杂SQL(对账周期汇总表)

SQL语句：

 select * from (  select  top 100 percent tt.cdCode,tt.cMatName,tt.cMatCode,tt.cMatCode2,tt.cdDate,tt.cj,tt.unitName,tt.ddsl,tt.price,tt.receiveGUID,tt.receiveItemCode,tt.je,tt.chepai,tt.empName,tt.empCode,tt.yfdj,tt.yfje,tt.qfzs,tt.bc,tt.bckk,tt.receiveItem,null as inCode,tt.inSum,tt.iniQty,null as outCode,tt.outiQty,tt.outPrice,tt.outje,tt.dzzq,tt.dzzqCode,tt.dzzqGUID,tt.sort, tt.cdCode as ordCode1, 3 as iPrecision  from (/*采购订单*/select distinct cdt.cdCode,cdt.cMatName,cdt.cMatCode,cdt.cMatCode2,cdt.cdDate,cdt.cjGUID,cdt.cj,cdt.unitName,cdt.ddsl,cdt.price,cdt.je,cdt.chepai,cdt.empName,cdt.empCode,cdt.yfdj,cdt.yfje,cdt.qfzs,cdt.bc,cdt.bckk,cdt.receiveItem,cdt.receiveItemCode,cdt.receiveGUID,cdt.inSum,cdt.iniQty,cdt.outiQty,cdt.outPrice,cdt.outje,cdt.dzzq,cdt.dzzqCode,cdt.dzzqGUID,cdt.sort from  (select top 100 percentord.cCode as cdCode, cm.cName as cMatName,cm.cCode as cMatCode,cm.cCode as cMatCode2,ord.dPODate as cdDate,ordl.cFree1 as cjGUID,cj.cName as cj,cmu.cName as unitName,ordl.iQTY as ddsl,ordl.iTaxPrice as price,(ordl.iQTY*ordl.iTaxPrice) as je,ordl.udef_001 as chepai,null as empName,null as empCode,null as yfdj,null as yfje,(ordl.iQTY*0.003) as qfzs,(insum.sumiQty-ordl.iQTY) as bc,((insum.sumiQty-ordl.iQTY)*ordl.iTaxPrice) as bckk,null AS receiveItem,null AS receiveItemCode,case when {shddCode_s:'0'} =(select cAcctIItemGuid from ST_StkRecord where cGUID = gen.cDMainID)  then   {shddCode_s:'0'}else nullendas receiveGUID,insum.sumiQty as inSum,null as iniQty,null as outiQty,null as outPrice,null as outje,null as dzzq,case when {DzzqCode_s:'0'} = (select dzzqt.cCode from ST_StkRecord stleft join AOS_BD_ENUMERATE dzzqt on dzzqt.cGuid = st.udef_0002 where st.cGUID = gen.cDMainID) then  {DzzqCode_s:'0'}else nullendas dzzqCode,null as dzzqGUID,ordl.cGUID+'1' as sortfrom PU_Order ord left join PU_OrderLine ordl on ord.cGUID =ordl.cHeadGUIDleft join CM_Material cm on ordl.cMatGUID=cm.cGUIDleft join BILL_GEN_RELATION_MAIN gen on gen.cSMainID = ord.cGUIDleft join  CM_Supplier sup on ord.cSupGUID=sup.cGUIDINNER JOIN CM_Unit mu ON mu.cGUID = ordl.cMUnitGUIDleft join CM_Project item on ordl.cItemGUID=item.cGUIDleft join AOS_BD_ENUMERATE cj on cj.cGUID = ordl.cFree1left join CM_Unit cmu on cmu.cGUID = ordl.cUnitGUIDleft join (select top 100 percentord.cCode as cdCode,SUM(stl.iQTY) as sumiQtyfrom PU_Order ord left join PU_OrderLine ordl on ord.cGUID =ordl.cHeadGUIDleft join BILL_GEN_RELATION_MAIN gen on gen.cSLineID = ordl.cGUIDleft join ST_StkRecordLine stl on stl.cGUID = gen.cDLineIDleft join ST_StkRecord st on st.cGUID = stl.cHeadGUIDgroup by ord.cCode)insum on insum.cdCode = ord.cCode)cdtunion all/*采购入库订单*/select cr.cdCode,cr.cMatName,cr.cMatCode,cr.cMatCode2,cr.cdDate,cr.cjGUID,cr.cj,cr.unitName,cr.ddsl,cr.price,cr.je,cr.chepai,cr.empName,cr.empCode,cr.yfdj,sum(isnull(cr.yfje,0)) as yfje,cr.qfzs,sum(isnull(cr.bc,0)) as bc,sum(isnull(cr.bckk,0)) as bckk,cr.receiveItem,cr.receiveItemCode,cr.receiveGUID,cr.inSum,SUM(isnull(cr.iniQty,0)) as iniQty,SUM(isnull(cr.outiQty,0)) as outiQty,cr.outPrice,SUM(isnull(cr.outje,0)) as outje,cr.dzzq,cr.dzzqCode,cr.dzzqGUID,cr.sort from (select top 100 percentord.cCode as cdCode,null as cMatName,NULL as cMatCode,cm.cCode as cMatCode2,ord.dPODate as cdDate,null as cjGUID,cj.cName as cj,null as unitName,null as ddsl,null as price,null as je,ordl.udef_001 as chepai,emp.cName as empName,null as empCode,stl.udef_0001 as yfdj,(stl.udef_0001*stl.iQTY) as yfje,null as qfzs,null as bc,null as bckk,cmp.cName AS receiveItem,cmp.cCode AS receiveItemCode,st.cAcctIItemGuid as receiveGUID,st.cBillCode as inCode,null as inSum,stl.iQTY as iniQty,null as outCode,null as outiQty,null as outPrice,null as outje,dzzqt.cName as dzzq,dzzqt.cCode as dzzqCode,dzzqt.cGUID as dzzqGUID,ordl.cGUID+'2' as sortfrom PU_Order ord left join PU_OrderLine ordl on ord.cGUID =ordl.cHeadGUIDleft join CM_Material cm on ordl.cMatGUID=cm.cGUIDleft join BILL_GEN_RELATION_MAIN gen on gen.cSLineID = ordl.cGUIDleft join ST_StkRecordLine stl on stl.cGUID = gen.cDLineIDleft join ST_StkRecord st on st.cGUID = stl.cHeadGUIDleft join AOS_BD_ENUMERATE cj on cj.cGUID = ordl.cFree1left join CM_Project cmp on cmp.cGUID = st.cAcctIItemGuidleft join AOS_BD_ENUMERATE dzzqt on dzzqt.cGuid = st.udef_0002left join CM_Employee emp on emp.cGUID = st.cEmpGUIDwhere st.cBilltype = '010')crgroup by cr.cdCode,cr.cMatName,cr.cMatCode,cr.cMatCode2,cr.cdDate,cr.cjGUID,cr.cj,cr.unitName,cr.ddsl,cr.price,cr.je,cr.chepai,cr.empName,cr.empCode,cr.yfdj,cr.qfzs,cr.receiveItem,cr.receiveItemCode,cr.receiveGUID,cr.inSum,cr.outPrice,cr.dzzq,cr.dzzqCode,cr.dzzqGUID,cr.sortunion all/*销售出库订单*/select xc.cdCode,xc.cMatName,xc.cMatCode,xc.cMatCode2,xc.cdDate,xc.cjGUID,xc.cj,xc.unitName,xc.ddsl,xc.price,xc.je,xc.chepai,xc.empName,xc.empCode,xc.yfdj,sum(isnull(xc.yfje,0)) as yfje,xc.qfzs,sum(isnull(xc.bc,0)) as bc,sum(isnull(xc.bckk,0)) as bckk,xc.receiveItem,xc.receiveItemCode,xc.receiveGUID,xc.inSum,SUM(isnull(xc.iniQty,0)) as iniQty,SUM(isnull(xc.outiQty,0)) as outiQty,xc.outPrice,SUM(isnull(xc.outje,0)) as outje,xc.dzzq,xc.dzzqCode,xc.dzzqGUID,xc.sortfrom (select top 100 percentord.cCode as cdCode,null as cMatName,null as cMatCode,cm.cCode as cMatCode2,ord.dPODate as cdDate,null as cjGUID,cj.cName as cj,null as unitName,null as ddsl,null as price,null as je,ordl.udef_001 as chepai,emp.cName as empName,emp.cCode as empCode,stl.udef_0001 as yfdj,null as yfje,null as qfzs,null as bc,null as bckk,cmp.cName AS receiveItem,cmp.cCode AS receiveItemCode,st.cAcctIItemGuid as receiveGUID,null as inCode,null as inSum,null as iniQty,stout.cBillCode as outCode,stlout.iQTY as outiQty,stlout.iTaxPrice as outPrice,(stlout.iQTY*stlout.iTaxPrice) as outje,dzzqt.cName as dzzq,dzzqt.cCode as dzzqCode,dzzqt.cGUID as dzzqGUID,ordl.cGUID+'3' as sortfrom PU_Order ord left join PU_OrderLine ordl on ord.cGUID =ordl.cHeadGUIDleft join CM_Material cm on ordl.cMatGUID=cm.cGUIDleft join BILL_GEN_RELATION_MAIN gen on gen.cSLineID = ordl.cGUIDleft join ST_StkRecordLine stl on stl.cGUID = gen.cDLineIDleft join ST_StkRecord st on st.cGUID = stl.cHeadGUIDleft join AOS_BD_ENUMERATE cj on cj.cGUID = ordl.cFree1left  join BILL_GEN_RELATION_MAIN gen1 on gen1.cSLineID = stl.cGUIDleft join ST_StkRecordLine stlout on stlout.cGUID = gen1.cDLineIDleft join ST_StkRecord stout on stout.cGUID = stlout.cHeadGUID and stout.cBillType='020'left join CM_Project cmp on cmp.cGUID = st.cAcctIItemGuidleft join AOS_BD_ENUMERATE dzzqt on dzzqt.cGuid = st.udef_0002left join CM_Employee emp on emp.cGUID = st.cEmpGUID) xc group by xc.cdCode,xc.cMatName,xc.cMatCode,xc.cMatCode2,xc.cdDate,xc.cjGUID,xc.cj,xc.unitName,xc.ddsl,xc.price,xc.je,xc.chepai,xc.empName,xc.empCode,xc.yfdj,xc.qfzs,xc.receiveItem,xc.receiveItemCode,xc.receiveGUID,xc.inSum,xc.outPrice,xc.dzzq,xc.dzzqCode,xc.dzzqGUID,xc.sort) tt      ) t    where  $between(t.cdDate,dDate,dDateTo)and $between(t.cdCode,cBillCode,cBillCodeTo)and $between(t.empCode,cEmpCode,cEmpCodeTo)and $between(t.cMatCode2,cMatCodeLower,cMatCodeUpper)and $equal(t.receiveGUID,cAcctIItemGuid)and $equal(t.dzzqCode,referDzzq)order by t.cdDate desc, t.cdCode desc,t.sort asc

form

<?xml version="1.0" encoding="UTF-8"?><Forms><form id="aos_business_Stkstatistic_orderInOut1" extend="pt_billsys_report2_list_template" implement="a6_business_sa_setPrintMemo_interface" desp="对账周期汇总表"><value name="sqlid">execute1.gridData3</value><!-- <value name="sqlid">execute.gridData1</value> -->    <value name="filterForm">business_PU_ordertj_apfilter_t</value><value name="pageSize">15</value><value name="hasPage">true</value><var name="printmemowids">dDate::日期:,dDateTo::到,cBillCode::订单号:,cBillCodeTo::到,cSupCode::供应商编码:,cSupCodeTo::到,cMatCodeLower::物品编码:,cMatCodeUpper::到</var><!--表头打印 不同控件用‘，’隔开，同一控件id和名称用‘::’隔开 --><var name="printmemolayout">topinfo</var><!-- 需要打印的表头信息所属layout,如不设置表头隐藏时也会打印表头--><toolbar buttons="btnFilter,common,btnRefresh,btnDetial,,btnPrint,btnExport,,btnHelp,btnExit"></toolbar><widgets>             <basic name="list" attr="page:true;"><col id="iPrecision" label="数量精度" hidden="true"/><col id='cj' label="厂家" /><col id='cdCode' label="采购订单号" /><col id='cdDate' label="订单日期" /><col id='cMatName' label="物品名称" /><col id='unitName' label="单位" /><col id='ddsl' label="订单数量" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='price' label="订单单价" editType="num" renderer="FloatRender" sum="false" attr="prec:iPrecision"  align="right"/><col id='je' label="订单金额" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='chepai' label="车牌号" /><col id='receiveItem' label="用料地点" /><col label="入库" id="stkin"><col id='inCode' label="入库单号" /><col id='iniQty' label="入库数量" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='empName' label="车管员" /><col id='yfdj' label="运费单价" editType="num" renderer="FloatRender" sum="false" attr="prec:iPrecision"  align="right"/><col id='yfje' label="运费金额" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='qfzs' label="千分之三" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='bc' label="磅差" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='bckk' label="磅差款" editType="num" renderer="FloatRender" sum="false" attr="prec:iPrecision"  align="right"/></col><col label="出库库" id="stkout"><col id='outCode' label="出库单号" /><col id='outiQty' label="出库数量" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/><col id='outPrice' label="出库单价" editType="num" renderer="FloatRender" sum="false" attr="prec:iPrecision"  align="right"/><col id='outje' label="出库金额" editType="num" renderer="FloatRender" sum="true" attr="prec:iPrecision"  align="right"/></col><col id='dzzq' label="对账周期" />             </basic></widgets><extendPoint><plugin type="com.aisino.a6.business.pu.statistic.plugin.PUOrderTjDealSumColPlugin" parent="Qry" idx="0"/></extendPoint><bind element="btnRefresh" event="click"><![CDATA[if(PT.f().getListIndex && PT.f().getListIndex()==1)w('list1').reload(null, function(){if(PT.f().afterRefreshList1)PT.f().afterRefreshList1();PT.v('refreshed',true);       PT.v('refreshedM',true);});elsew('list').reload(null, function(){if(PT.f().afterRefreshList)PT.f().afterRefreshList();});]]></bind></form>    <form id="business_PU_ordertj_apfilter_t" extend="pt_billsys_report2_queryplan_template"  implement="a6_queryplan_template_interface"  desp="采购订单查询条件">       <value name="entities">PU_Order,PU_OrderLine,CM_Supplier,cm_material</value>          <value name="queryplan">cBillCode,cBillCodeTo;cSupCode,cSupCodeTo;cDeptCode,cDeptCodeTo;cEmpCode,cEmpCodeTo;cMatCodeLower,cMatCodeUpper;</value>        <var name="datefromto">dDate,dDateTo;</var>   <widgets>      <layout type="BandLayout" name="common1" layout="common" ><basic name="dDate" label="订单日期" widget="DateChooser" default="Sess:firstday"/><basic name="dDateTo" label="至" widget="DateChooser"/><basic name="cBillCode" label="订单号" widget="ReferEdit" attr="popbit:1;submitCode:true;strict:false">     <valueFetcher id="common_refer_bus_cbillcode" ctg="PU_Order.cCode"/>     </basic><basic name="cBillCodeTo" label="至"  widget="ReferEdit" attr="popbit:1;submitCode:true;strict:false">     <valueFetcher id="common_refer_bus_cbillcode" ctg="PU_Order.cCode"/>     </basic><basic name="cEmpCode" label="车管员"  widget="ReferEdit" attr="submitCode:true;popbit:1;" referWidgets="showDisableControl"><valueFetcher id="employee" /></basic><basic name="cEmpCodeTo" label="至"  widget="ReferEdit" attr="submitCode:true;popbit:1;" referWidgets="showDisableControl"><valueFetcher id="employee" /></basic><basic name="cMatCodeLower" label="物品编码" referWidgets="iServisFlag,showDisableControl" attr="referForm:refer_material;popbit:1;submitCode:true;listWidth:250px" widget="ReferEdit">                   <valueFetcher id="material"/></basic>    <basic name="cMatCodeUpper" label="至" referWidgets="iServisFlag,showDisableControl" attr="referForm:refer_material;popbit:1;submitCode:true;listWidth:250px" widget="ReferEdit">                   <valueFetcher id="material"/>            </basic> <basic name="cAcctIItemGuid" label="收货地点" attr="bit:1;referForm:refer_project;" widget="ReferEdit" referWidgets="showDisableControl"><valueFetcher id="project"/></basic><basic name="referDzzq" label="对账周期" widget="ReferEdit" attr="submitCode:true;popbit:1;" ><valueFetcher type="com.aisino.platform.view.basicWidget.fetcher.DbReferFetcher">        <value name="referForm">pt_dzzq1</value></valueFetcher></basic>  <layout type="BandLayout" name="common2" layout="common2" > <basic label="单行显示模式" name="iDanHang" widget="TrueFalseBox" attr="truevalue:1;falsevalue:0;leftSpaceWidth:65px" default="0"/>     <!-- 劳务物品属性，订单为显示所有物品 --><basic name="iServisFlag" label="劳务物品属性" default="1" widget="Hidden"></basic><basic name="openflag" label="" default="1" widget="Hidden"></basic><basic name="shddCode_s" label="" default="0" widget="Hidden"></basic><basic name="DzzqCode_s" label="" default="2017060111" widget="Hidden"></basic> <basic name="cBatchGUID" label="批次"/> <basic name="receiveStatus" label="到货状态" widget="Combox" disabled="true" > <valueFetcher type="com.aisino.platform.view.basicWidget.fetcher.StaticFetcher"><value name="all">全部到货</value><value name="part">部分到货</value><value name="not">未到货</value></valueFetcher></basic><basic name="stkinStatus" label="入库状态" widget="Combox" disabled="true" ><valueFetcher type="com.aisino.platform.view.basicWidget.fetcher.StaticFetcher"><value name="all">全部入库</value><value name="part">部分入库</value><value name="not">未入库</value></valueFetcher></basic> <basic name="invoiceStatus" label="开票状态" widget="Combox" disabled="true" ><valueFetcher type="com.aisino.platform.view.basicWidget.fetcher.StaticFetcher"><value name="all">全部开票</value><value name="part">部分开票</value><value name="not">未开票</value></valueFetcher></basic> <basic name="payStatus" label="付款状态" widget="Combox" disabled="true" ><valueFetcher type="com.aisino.platform.view.basicWidget.fetcher.StaticFetcher"><value name="all">全部付款</value><value name="part">部分付款</value><value name="not">未付款</value></valueFetcher></basic><basic name="showDisableControl" label="显示禁用" layout="terms" default="true" widget="Hidden"></basic>         </layout>          </layout>            </widgets>  <extendPoint>        <plugin type="com.aisino.a6.business.pu.statistic.plugin.ManageFilterPlugin" parent="queryPlan"></plugin><plugin type="com.aisino.a6.business.pu.statistic.plugin.FilterShowResult" onEvent="filter"></plugin> </extendPoint>   <bind element="this" event="onCreate" extendway="after"><![CDATA[PT.hideLayout('common2');    PT.wid('sCFList').hideColumnByName('groupsum',true);if(wg('iDanHang')==1){ w('receiveStatus').setDisabled(false);  w('stkinStatus').setDisabled(false); w('invoiceStatus').setDisabled(false); w('payStatus').setDisabled(false); }else if(wg('iDanHang')==0){ w('receiveStatus').set(null); w('stkinStatus').set(null); w('invoiceStatus').set(null); w('payStatus').set(null); w('receiveStatus').setDisabled(true); w('stkinStatus').setDisabled(true); w('invoiceStatus').setDisabled(true); w('payStatus').setDisabled(true); } if(wg('openflag')==1 && wg('iDanHang')==0){    w('sTList').addBlankRow();    var list=w('sTList').data;     w('sTList').cv('name','订单号',list[0]);     w('sTList').cv('ownfield','',list[0]);     w('sTList').cv('code','ordcode1',list[0]);     w('sTList').cv('sort',0,list[0]);     w('sTList').cv('group',1,list[0]);     //w('sTList').addBlankRow();     w('sTList').cv('name','物品名称',list[1]);     w('sTList').cv('ownfield','',list[1]);     w('sTList').cv('code','matname1',list[1]);     w('sTList').cv('sort',0,list[1]);     w('sTList').cv('group',1,list[1]);     ws('openflag',2);     }        ]]>        </bind>     <bind element="iDanHang" event="change"><![CDATA[ if(wg('iDanHang')==1){ w('receiveStatus').setDisabled(false); w('stkinStatus').setDisabled(false); w('invoiceStatus').setDisabled(false); w('payStatus').setDisabled(false); }else if(wg('iDanHang')==0){ w('receiveStatus').set(null); w('stkinStatus').set(null); w('invoiceStatus').set(null); w('payStatus').set(null); w('receiveStatus').setDisabled(true); w('stkinStatus').setDisabled(true); w('invoiceStatus').setDisabled(true); w('payStatus').setDisabled(true); }   if(wg('iDanHang')==0){        w('sTList').clear();    w('sTList').addBlankRow();    var list=w('sTList').data;     w('sTList').cv('name','订单号',list[0]);     w('sTList').cv('ownfield','',list[0]);     w('sTList').cv('code','ordcode1',list[0]);     w('sTList').cv('sort',0,list[0]);     w('sTList').cv('group',1,list[0]);     //w('sTList').addBlankRow();     w('sTList').cv('name','物品名称',list[1]);     w('sTList').cv('ownfield','',list[1]);     w('sTList').cv('code','matname1',list[1]);     w('sTList').cv('sort',0,list[1]);     w('sTList').cv('group',1,list[1]);}     else{        w('sTList').clear();         }   PT.ns('filter');   ]]></bind><!-- 获取对账周期和用料地点，传递给sql语句进行筛选赋值 --> <bind element="ok" event="click" extendway="before">                 var map = w('referDzzq').getData();                var code =map['code'];                  PT.setValue('DzzqCode_s',code);                                 var map = w('cAcctIItemGuid').getData();                 var guid =map['value'];                      PT.setValue('shddCode_s',guid);            </bind>   </form>     <form id="pt_dzzq1" extend="SReferTemplate" desp="对账周期参照"><!-- <value name="sqlid">test_refer.getCity</value> --><value name="sql"><![CDATA[  select distinct a.cguid,a.cCode,a.cNamefrom AOS_BD_ENUMERATE awhere a.cEnTypeGuid='637937323770978207']]></value><value name="filterCol">ccode</value><toolbar></toolbar><widgets><basic name="list"><col label="编码" id="cCode" width="120px" widget="Hidden"></col><col label="名称" id="cName" width="180px"></col></basic></widgets></form></Forms>