第一个:

SELECT

SCZD.生产组织,
SCZD.生产订单日期,
SCZD.生产订单编号,
SCZD.单据类型,
SCZD.业务类型,
SCZD.单据状态,
SCZD.指令单号,
SCZD.生产订单分录内码,
SCZD.物料名称,
SCZD.物料编码,
SCZD.规格型号,
SCZD.生产订单数量,
SCZD.色名,
SCZD.机台,
SCZD.完成数量,
SCZD.成检数量,
SCZD.损耗数量,
(SCLL.实领数量 - SCTL.实退数量 + SCBL.实补数量) 领料数量

FROM (

SELECT

TOOL.FNAME 生产组织,
TPM.FDATE 生产订单日期,
TPM.FBILLNO 生产订单编号,
TBBL.FNAME 单据类型,
TPM.F_PUEV_ASSISTANT2 业务类型,
TPM.FDOCUMENTSTATUS 单据状态,
TPME.F_PUEV_TEXT10 指令单号,
TPME.FENTRYID 生产订单分录内码,
TBML.FNAME 物料名称,
TBM.FNUMBER 物料编码,
TBML.FSPECIFICATION 规格型号,
TPME.FQTY 生产订单数量,
TPME.F_VVDH_TEXT1 色名,
TPME.F_VVDH_ASSISTANT 机台,
TRME.FFINISHQTY 完成数量,
TRME.FQUAQTY 成检数量,
TRME.FFAILQTY 损耗数量

FROM
T_PRD_MO TPM --生产订单表头
LEFT JOIN T_PRD_MOENTRY TPME ON TPM.FID = TPME.FID --生产订单明细
LEFT JOIN t_org_organizations TOO ON TPM.FPRDORGID = TOO.FORGID --组织机构
LEFT JOIN t_org_organizations_l TOOL ON TOO.FORGID  = TOOL.FORGID AND TOOL.FLOCALEID = '2052' --组织机构多语言
LEFT JOIN T_BAS_BILLTYPE TBB ON TPM.FBILLTYPE = TBB.FBILLTYPEID --单据类型
LEFT JOIN T_BAS_BILLTYPE_L TBBL ON TBB.FBILLTYPEID = TBBL.FBILLTYPEID AND TBBL.FLOCALEID = '2052' --单据类型多语言表
LEFT JOIN T_BD_MATERIAL TBM ON TPME.FMATERIALID = TBM.FMATERIALID --物料
LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言
LEFT JOIN T_PRD_MORPTENTRY TRME ON TPME.FENTRYID = TRME.FMOENTRYID --生产汇报单明细
)SCZD

LEFT JOIN (

SELECT

TPPD.FENTRYID 生产领料单分录内码,
TPPD.FMOENTRYID 生产订单分录内码,
COALESCE(TPPD.FACTUALQTY,0) 实领数量,
TBML.FNAME 物料名称,
TBM.FNUMBER 物料编码

FROM

T_PRD_PICKMTRLDATA TPPD --生产领料单明细

LEFT JOIN T_BD_MATERIAL TBM ON TPPD.FMATERIALID = TBM.FMATERIALID --物料
LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言

)SCLL ON SCZD.生产订单分录内码 = SCLL.生产订单分录内码
AND    SCZD.物料名称 = SCLL.物料名称

LEFT JOIN (
SELECT

TPRME.FENTRYID 生产退料单分录内码,
TPRME.FMOENTRYID 生产订单分录内码,
COALESCE(TPRME.FQTY,0) 实退数量,
TBML.FNAME 物料名称,
TBM.FNUMBER 物料编码

FROM

T_PRD_RETURNMTRLENTRY TPRME

LEFT JOIN T_BD_MATERIAL TBM ON TPRME.FMATERIALID = TBM.FMATERIALID --物料
LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言

)SCTL ON SCZD.生产订单分录内码 = SCTL.生产订单分录内码
AND    SCZD.物料名称 = SCTL.物料名称

LEFT JOIN (

SELECT

TPFD.FENTRYID 生产补料单分录内码,
TPFD.FMOENTRYID 生产订单分录内码,
COALESCE(TPFQ.FACTUALQTY,0) 实补数量,
TBML.FNAME 物料名称,
TBM.FNUMBER 物料编码

FROM

T_PRD_FEEDMTRLDATA  TPFD --生产领料单明细
LEFT JOIN T_PRD_FEEDMTRLDATA_Q TPFQ ON TPFD.FENTRYID = TPFQ.FENTRYID
LEFT JOIN T_BD_MATERIAL TBM ON TPFD.FMATERIALID = TBM.FMATERIALID --物料
LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言

)SCBL ON SCZD.生产订单分录内码 = SCBL.生产订单分录内码
AND    SCZD.物料名称 = SCBL.物料名称
AND SCZD.物料编码 LIKE '%#物料编码#%'
AND    SCZD.物料名称 LIKE '%#物料名称#%'
AND    SCZD.生产订单编号 LIKE '%#生产订单编号#%'
AND SCZD.指令单号 LIKE '%#指令单号#%'
AND SCZD.生产订单日期 = '#生产订单日期#'

第二个

SELECT

SCRBB.生产组织,

SCRBB.生产订单日期,

SCRBB.生产订单编号,

SCRBB.单据类型,

SCRBB.业务类型,

SCRBB.单据状态,

SCRBB.指令单号,

SCRBB.生产订单分录内码,

SCRBB.物料名称,

SCRBB.物料编码,

SCRBB.规格型号,

SCRBB.生产订单数量,

SCRBB.色名,

SCRBB.机台,

SCRBB.完成数量,

SCRBB.成检数量,

SCRBB.损耗数量,

(SCRBB.实领数量 - SCRBB.实退数量 + SCRBB.实补数量) 领料数量

FROM

(

SELECT

SCZD.生产组织,

SCZD.生产订单日期,

SCZD.生产订单编号,

SCZD.单据类型,

SCZD.业务类型,

SCZD.单据状态,

SCZD.指令单号,

SCZD.生产订单分录内码,

SCZD.物料名称,

SCZD.物料编码,

SCZD.规格型号,

SCZD.生产订单数量,

SCZD.色名,

SCZD.机台,

SCZD.完成数量,

SCZD.成检数量,

SCZD.损耗数量,

COALESCE(SCLL.实领数量,0) 实领数量,

COALESCE(SCTL.实退数量,0) 实退数量,

COALESCE(SCBL.实补数量,0) 实补数量

FROM (

SELECT

TOOL.FNAME 生产组织,

TPM.FDATE 生产订单日期,

TPM.FBILLNO 生产订单编号,

TBBL.FNAME 单据类型,

TPM.F_PUEV_ASSISTANT2 业务类型,

TPM.FDOCUMENTSTATUS 单据状态,

TPME.FENTRYID 生产订单分录内码,

TPPT.FENTRYID 用料清单分录内码,

TPME.F_PUEV_TEXT10 指令单号,

TBML.FNAME 物料名称,

TBM.FNUMBER 物料编码,

TBML.FSPECIFICATION 规格型号,

TPME.FQTY 生产订单数量,

TPME.F_VVDH_TEXT1 色名,

TBAAEL.FDATAVALUE 机台,

TRME.FFINISHQTY 完成数量,

TRME.FQUAQTY 成检数量,

TRME.FFAILQTY 损耗数量

FROM

T_PRD_MO TPM --生产订单表头

LEFT JOIN T_PRD_MOENTRY TPME ON TPM.FID = TPME.FID --生产订单明细

LEFT JOIN t_org_organizations TOO ON TPM.FPRDORGID = TOO.FORGID --组织机构

LEFT JOIN t_org_organizations_l TOOL ON TOO.FORGID = TOOL.FORGID AND TOOL.FLOCALEID = '2052' --组织机构多语言

LEFT JOIN T_BAS_BILLTYPE TBB ON TPM.FBILLTYPE = TBB.FBILLTYPEID --单据类型

LEFT JOIN T_BAS_BILLTYPE_L TBBL ON TBB.FBILLTYPEID = TBBL.FBILLTYPEID AND TBBL.FLOCALEID = '2052' --单据类型多语言表

LEFT JOIN T_BD_MATERIAL TBM ON TPME.FMATERIALID = TBM.FMATERIALID --物料

LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言

LEFT JOIN T_PRD_MORPTENTRY TRME ON TPME.FENTRYID = TRME.FMOENTRYID --生产汇报单明细

LEFT JOIN T_PRD_MORPT TRM ON TRME.FID = TRM.FID --生产汇报单表头

LEFT JOIN T_BAS_ASSISTANTDATAENTRY TBAAE ON TPME.F_VVDH_ASSISTANT = TBAAE.FMasterId --辅助资料

LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L TBAAEL ON TBAAE.FENTRYID = TBAAEL.FENTRYID AND TBAAEL.FLOCALEID = '2052' --辅助资料多语言

LEFT JOIN T_PRD_PPBOMENTRY TPPT ON T

我只是多加了一层子查询为了计算,我在数据库运行都是没问题的

产品问题直接sql账表过滤问题

66 产品问题 直接sql账表过滤问题

恭喜您！

产品问题
直接sql账表过滤问题

66 产品问题直接sql账表过滤问题