求“出入库流水帐”的SQL语句

lg0219

哪位仁兄给一段"出入库流水帐"的SQL语句。
别让我跟踪，跟踪出来的分析太累。

elm

select c.dDate,b.cInvCode,b.cinvname,b.cinvstd,a.cBatch,a.iquantity, b.ccomunitcode      
  from rdRecord c, rdRecords a left join inventory b
    on a.cinvcode = b.cinvcode
    where c.id = a.id and c.bRdFlag='1'

后面继续写条件, 其中bRdFlag=1入库, =0出库

lg0219

好的，谢谢，我试试看。

lg0219

不行啊，和ERP中的“出入库流水帐”查出的不一样。

cldzyc

现在希望能够定时自动导出出入库流水帐，希望有朋友能够帮忙

a627591579

感谢分享 金币不够多发帖

15058087871

自己吧查询所有出入库单据的代码写出来，用UNION ALL，拼接起来就好了

cldzyc

我也想使用SQL语言定期导出出入库流水账为EXCEL格式，便于其他人使用。

sztlbb

这个肯定要跟踪，这种帐肯定是语句，不可能是这个简单的视图

yyyy2323

#在这里快速回复#  SELECT r.id AS ID , R.dDate ,rs.autoid, r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,  (case when R.cvouchtype =N'32' and (isnull(R.csource,N'') =N'发货单' or isnull(R.csource,N'') =N'委托代销') then isnull(Rs.cbdlcode,N'') else IsNull(R.cBusCode,N'') end) AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,   convert(nvarchar(2),N'') AS DDH, convert(nvarchar(2),N'') AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, R.cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,   Null AS 委外订单号, convert(nvarchar(60),N'') AS 生产订单号,  Null As 币种, Null AS 汇率, Null AS 税率,  Null AS 原币无税单价, Null AS 原币无税金额, Null AS 原币税额,Null As 原币价税合计,  Null AS 本币税额,Null As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,  Null   as cVenCode ,rs.cvmivencode ,rs.cAssUnit   FROM   RdRecord32 R  with (nolock) left join  RdRecords32 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))   union all    SELECT r.id AS ID , R.dDate ,rs.autoid, r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,   IsNull(R.cBusCode,N'')  AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,   convert(nvarchar(2),N'') AS DDH, convert(nvarchar(2),N'') AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, Rs.cMoLotCode as cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,    Rs.comcode  AS 委外订单号, IsNull(cmocode,N'') AS 生产订单号,  Null As 币种, Null AS 汇率, Null AS 税率,  Null AS 原币无税单价, Null AS 原币无税金额, Null AS 原币税额,Null As 原币价税合计,  Null AS 本币税额,Null As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,    R.cVenCode   as cVenCode ,rs.cvmivencode ,rs.cAssUnit   FROM   RdRecord11 R  with (nolock) left join  RdRecords11 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))     union all     SELECT r.id AS ID , R.dDate ,rs.autoid, r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,   IsNull(R.cBusCode,N'')  AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,   convert(nvarchar(2),N'') AS DDH, convert(nvarchar(2),N'') AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, Rs.cMoLotCode as cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,   Null AS 委外订单号, IsNull(cmocode,N'') AS 生产订单号,  Null As 币种, Null AS 汇率, Null AS 税率,  Null AS 原币无税单价, Null AS 原币无税金额, Null AS 原币税额,Null As 原币价税合计,  Null AS 本币税额,Null As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,  Null   as cVenCode ,rs.cvmivencode ,rs.cAssUnit   FROM   RdRecord10 R  with (nolock) left join  RdRecords10 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))     union all     SELECT r.id AS ID , R.dDate ,rs.autoid, r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,   IsNull(R.cBusCode,N'')  AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,   convert(nvarchar(2),N'') AS DDH, convert(nvarchar(2),N'') AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, R.cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,   Null AS 委外订单号, convert(nvarchar(60),N'') AS 生产订单号,  Null As 币种, Null AS 汇率, Null AS 税率,  Null AS 原币无税单价, Null AS 原币无税金额, Null AS 原币税额,Null As 原币价税合计,  Null AS 本币税额,Null As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,  Null   as cVenCode ,rs.cvmivencode ,rs.cAssUnit   FROM   RdRecord09 R  with (nolock) left join  RdRecords09 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))      union all         SELECT r.id AS ID , R.dDate ,rs.autoid, r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,   IsNull(R.cBusCode,N'')  AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,   convert(nvarchar(2),N'') AS DDH, convert(nvarchar(2),N'') AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, R.cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,   Null AS 委外订单号, convert(nvarchar(60),N'') AS 生产订单号,  Null As 币种, Null AS 汇率, Null AS 税率,  Null AS 原币无税单价, Null AS 原币无税金额, Null AS 原币税额,Null As 原币价税合计,  Null AS 本币税额,Null As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,  (case when isnull(R.cbustype,N'')=N'调拨入库' then rs.cbvencode else R.cVenCode end)   as cVenCode ,rs.cvmivencode ,rs.cAssUnit   FROM   RdRecord08 R  with (nolock) left join  RdRecords08 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))     union all     SELECT r.id AS ID , R.dDate ,rs.autoid,r.brdflag,rs.dvdate,Rs.dmadedate AS dmadedate ,ISNULL(Rs.iExpiratDateCalcu,0) as iExpiratDateCalcu , Rs.cExpirationdate as cExpirationdate,Rs.cCiqBookCode ,rs.cbmemo as cbmemo,  r.cvouchtype, R.cBusType, R.cMemo ,   IsNull(R.cBusCode,N'')  AS cBusCode,  R.cWhCode, R.cCode,   r.crdcode,r.cdepcode,r.cpersoncode,r.cPTCode,  case when isnull(Rs.iposid,0) <> 0 then Rs.cpoid else convert(nvarchar(2),N'') end AS DDH, isnull(Rs.cbarvcode,convert(nvarchar(2),N'')) AS DHDH, R.cSTCode,  R.cBillCode, r.ccuscode,   R.cDLCode, R.cProBatch,R.cHandler,R.dVeriDate,  isnull(r.cDefine1,N'') as cDefine1,isnull(r.cDefine2,N'') as cDefine2,isnull(r.cDefine3,N'') as cDefine3,isnull(r.cDefine4,Null) as cDefine4,isnull(r.cDefine7,Null) as cDefine7,isnull(r.cDefine9,N'') as cDefine9,isnull(r.cDefine16,Null) as cDefine16,isnull(rs.cDefine37,Null) as cDefine37,Rs.cBatchProperty1,Rs.cBatchProperty2,Rs.cBatchProperty3,Rs.cBatchProperty4,Rs.cBatchProperty5,Rs.cBatchProperty6,Rs.cBatchProperty7,Rs.cBatchProperty8,Rs.cBatchProperty9,Rs.cBatchProperty10,  Rs.cInvCode AS cInvCode,   rs.iquantity,rs.inum ,  cFree1, Rs.iUnitCost , Rs.iPrice , Rs.iPUnitCost, Rs.iPPrice ,   Rs.cBatch AS PH, Rs.citemcode as 项目编码,Rs.cName AS XMMC, rs.citem_class as 项目大类编码,Rs.cItemCName AS XMDL,  Rs.cbAccounter AS JZR,   R.cMaker AS ZDR , Rs.cCheckPersonCode,Rs.cCheckCode,Rs.dCheckDate,Rs.cRejectCode, rs.iordertype,iordercode,rs.iorderseq,   (case when isnull(R.cvouchtype,N'')=N'01' and isnull(R.cbustype,N'')=N'委外加工' then Rs.cpoid else Rs.comcode end) AS 委外订单号, convert(nvarchar(60),N'') AS 生产订单号,  R.cExch_Name As 币种, R.iExchRate AS 汇率, R.iTaxRate AS 税率,  Rs.ioricost AS 原币无税单价, Rs.iorimoney AS 原币无税金额, Rs.ioritaxprice AS 原币税额,Rs.iorisum As 原币价税合计,  Rs.itaxprice AS 本币税额,Rs.isum As 本币价税合计, Rs.iNQuantity,Rs.INNum,rs.isotype,rs.csocode,rs.isoseq,rs.isodid,    R.cVenCode   as cVenCode ,rs.cvmivencode ,rs.cAssUnit    FROM   RdRecord01 R  with (nolock) left join  RdRecords01 Rs with (nolock) on R.id=rs.id   left join warehouse w on r.cwhcode = w.cwhcode   left join factory  on w.cfactorycode = factory.cfactorycode   Where 1=1   And (R.dDate >= N'2023-01-23' and R.dDate <= N'2024-10-23') And ((Rs.cInvCode >= N'200101000008') And (Rs.cInvCode <= N'200101000008'))