---生成材料出库单主表数据 insert into rdrecord11(VT_ID,cPsPcode,cMPoCode,iproorderid,iMQuantity,ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode, cRdCode,cDepCode,cHandler,cMaker,dVeriDate,dnmaketime,dnverifytime,iverifystate,iswfcontrolled,cModifyPerson, bpufirst,biafirst,bIsSTQc,bOMFirst,bFromPreYear,bIsComplement,iDiscountTaxType,ireturncount,bTransFlag, bredvouch,bmotran,bHYVouch,iPrintCount,csysbarcode,cCheckSignFlag) select '65' VT_ID,b.InvCode cPsPcode,c.MoCode cMPoCode,c.MoId iproorderid,b.Qty iMQuantity, case when (select MAX(ID) from ( select isnull(MAX(ID),0) ID from rdrecord11 union all select isnull(MAX(ID),0) ID from rdrecord10 union all select isnull(MAX(ID),0) ID from rdrecord09 union all select isnull(MAX(ID),0) ID from rdrecord01 union all select isnull(MAX(ID),0) ID from rdrecord08 union all select isnull(MAX(ID),0) ID from rdrecord32 union all select isnull(MAX(ID),0) ID from rdrecord34) a )=0 then 1000000000+ROW_NUMBER() over (order by b.moid) else (select MAX(ID) from ( select isnull(MAX(ID),0) ID from rdrecord11 union all select isnull(MAX(ID),0) ID from rdrecord10 union all select isnull(MAX(ID),0) ID from rdrecord09 union all select isnull(MAX(ID),0) ID from rdrecord01 union all select isnull(MAX(ID),0) ID from rdrecord08 union all select isnull(MAX(ID),0) ID from rdrecord32 union all select isnull(MAX(ID),0) ID from rdrecord34) a )++ROW_NUMBER() over (order by b.moid) end ID,0 bRdFlag,'11' cVouchType,'领料' cBusType,'生产订单' cBusType, a.whcode cWhCode, ---仓库默认01 d.StartDate ddate, ---制单日期，取生产订单开工日期 case when (select isnull(MAX(ccode),'') from rdrecord11)='' then right(10000000000+ROW_NUMBER() over (order by b.moid),10) else right(10000000000+(select isnull(MAX(ccode),0) from rdrecord11)+ROW_NUMBER() over (order by b.moid),10) end ccode, ---单据号 '04' cRdCode, ----出库类别，材料出库'21' b.MDeptCode cDepCode, ---部门，取生产订单部门 '汪沛' cHandler,----审核人 '周炯' cMaker,----制单人 d.StartDate dVeriDate, ---审核日期，与制单日期一致 d.StartDate+0.46 dnmaketime, ---制单时间 d.StartDate+0.65 dnverifytime, ---审核时间 0 iverifystate,0 iswfcontrolled,'' cModifyPerson, 0 bpufirst,0 biafirst,0 bIsSTQc,0 bOMFirst,0 bFromPreYear,0 bIsComplement,0 iDiscountTaxType,0 ireturncount,0 bTransFlag, 0 bredvouch,0 bmotran,0 bHYVouch,0 iPrintCount, '||st11|'+case when (select isnull(MAX(ccode),'') from rdrecord11)='' then right(10000000000+ROW_NUMBER() over (order by b.moid),10) else right(10000000000+(select isnull(MAX(ccode),0) from rdrecord11)+ROW_NUMBER() over (order by b.moid),10) end csysbarcode, ----单据号条码 null cCheckSignFlag from mom_orderdetail b left join mom_order c on b.MoId=c.MoId left join mom_morder d on b.MoId=d.MoId
left join mom_moallocate a on b.ModId=a.ModId where d.StartDate >='2019-01-01 00:00:00' and d.StartDate <='2019-01-01 00:00:00' and a.whcode = 'hs034'
正确的执行结果应该是会生成198条记录，但是现在执行出来的结果是3366.我看了一下每个结果都有重复了17次，正常应该只有一条数据，请大神帮我看看问题出来哪里了。
这个你要看一下你的查询语句，查询出来的数据肯定是重复了（联表条件满足出现了一对多的数据），根据你具体的业务用distinct 或者 group by 去重
这个密密麻麻一片 都没法看 你把它输出成代码段格式化一下
insert into rdrecord11(VT_ID,cPsPcode,cMPoCode,iproorderid,iMQuantity,ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,
cRdCode,cDepCode,cHandler,cMaker,dVeriDate,dnmaketime,dnverifytime,iverifystate,iswfcontrolled,cModifyPerson,
bpufirst,biafirst,bIsSTQc,bOMFirst,bFromPreYear,bIsComplement,iDiscountTaxType,ireturncount,bTransFlag,
bredvouch,bmotran,bHYVouch,iPrintCount,csysbarcode,cCheckSignFlag)
select '65' VT_ID,b.InvCode cPsPcode,c.MoCode cMPoCode,c.MoId iproorderid,b.Qty iMQuantity,
case when (select MAX(ID) from (
select isnull(MAX(ID),0) ID from rdrecord11
union all
select isnull(MAX(ID),0) ID from rdrecord10
union all
select isnull(MAX(ID),0) ID from rdrecord09
union all
select isnull(MAX(ID),0) ID from rdrecord01
union all
select isnull(MAX(ID),0) ID from rdrecord08
union all
select isnull(MAX(ID),0) ID from rdrecord32
union all
select isnull(MAX(ID),0) ID from rdrecord34) a )=0 then 1000000000+ROW_NUMBER() over (order by b.moid)
else (select MAX(ID) from (
select isnull(MAX(ID),0) ID from rdrecord11
union all
select isnull(MAX(ID),0) ID from rdrecord10
union all
select isnull(MAX(ID),0) ID from rdrecord09
union all
select isnull(MAX(ID),0) ID from rdrecord01
union all
select isnull(MAX(ID),0) ID from rdrecord08
union all
select isnull(MAX(ID),0) ID from rdrecord32
union all
select isnull(MAX(ID),0) ID from rdrecord34) a )++ROW_NUMBER() over (order by b.moid) end ID,0 bRdFlag,'11' cVouchType,'领料' cBusType,'生产订单' cBusType,
a.whcode cWhCode, ---仓库默认01
d.StartDate ddate, ---制单日期，取生产订单开工日期
case when (select isnull(MAX(ccode),'') from rdrecord11)='' then right(10000000000+ROW_NUMBER() over (order by b.moid),10)
else right(10000000000+(select isnull(MAX(ccode),0) from rdrecord11)+ROW_NUMBER() over (order by b.moid),10) end ccode, ---单据号
'04' cRdCode, ----出库类别，材料出库'21'
b.MDeptCode cDepCode, ---部门，取生产订单部门
'汪沛' cHandler,----审核人
'周炯' cMaker,----制单人
d.StartDate dVeriDate, ---审核日期，与制单日期一致
d.StartDate+0.46 dnmaketime, ---制单时间
d.StartDate+0.65 dnverifytime, ---审核时间
0 iverifystate,0 iswfcontrolled,'' cModifyPerson,
0 bpufirst,0 biafirst,0 bIsSTQc,0 bOMFirst,0 bFromPreYear,0 bIsComplement,0 iDiscountTaxType,0 ireturncount,0 bTransFlag,
0 bredvouch,0 bmotran,0 bHYVouch,0 iPrintCount,
'||st11|'+case when (select isnull(MAX(ccode),'') from rdrecord11)='' then right(10000000000+ROW_NUMBER() over (order by b.moid),10)
else right(10000000000+(select isnull(MAX(ccode),0) from rdrecord11)+ROW_NUMBER() over (order by b.moid),10) end csysbarcode, ----单据号条码
null cCheckSignFlag
from mom_orderdetail b
left join mom_order c on b.MoId=c.MoId
left join mom_morder d on b.MoId=d.MoId
left join mom_moallocate a on b.ModId=a.ModId
where d.StartDate >='2019-01-01 00:00:00' and d.StartDate <='2019-01-01 00:00:00' and a.whcode = 'hs034'