用友软件股份有限公司呼叫中心169号坐席李春华2010年04月26日存货明细账中显示记录重复问题测试环境：WINDOWSXP+SP3补丁：没有打过任何890补丁问题现象：先进先出法，有一张采购入库单记账并生成凭证了，在存货明细账中显示记录重复，同一张单据显示两行记录，其他单据都显示正常。指导客户在存货核算明细账中按照此采购入库单中存货编码=01019002065查询1月份有两条相同的记录，但是凭证号不同。指导客户查询存货核算中的收发存汇总表按照存货编码=01019002065查询，发现收发存汇总表中显示的数据正确。这说明不是此单据在记账时发生重复记账导致。指导客户在存货明细账中选择这两条相同的记录联查单据，发现是同一张采购入库指导客户在存货明细账中选择这两条相同的记录但是凭证号不同，一个为0028，一个为0130联查凭证，发现无论选择哪条记录是查询出来的凭证都是130号。指导客户在总账查询凭证中查询到0130号凭证点击联查原始单据时查询出来的原始单据就是之前查询的采购入库单，但是130号凭证显示凭证分录的却是产成品入库单生成的凭证分录。和客户沟通，客户说这张0130号凭证是通过总账工具从其他帐套导入的的凭证。指导客户在存货核算的凭证查询中将0028号凭证删除。但是发现在删除0028凭证后，在总账中查询发现0130号凭证也自动作废了。指导客户重新在存货核算中将此采购入库单生成凭证后，再到存货明细账中查询，发现显示正常。在总账中重新录入0130号凭证即可。那么是什么原因导致在存货明细账中没有重复记录但是在查询存货明细账时还会出现这样的情况呢？怀疑是导入凭证时0130号凭证在GL_ACCVOUCH表中的coutno_id和0028号凭证的一致导致。分析出现此问题的原因：1.首先在后台跟踪查询存货明细帐的执行语句。SELECTIA_Subsidiary.dKeepDate,month(dkeepdate)AsiMonth,day(dkeepdate)AsiDay,A.csign+''+Cast(A.ino_idAsnvarchar(10))AsiPzNo,IA_Subsidiary.cInvHead,IA_Subsidiary.cDifHead,IA_Subsidiary.cWhCode,IA_Subsidiary.cInvCode,IA_Subsidiary.cVouCode,IA_Subsidiary.dVouDate,Warehouse.cWhName,IA_Subsidiary.cBusCode,IA_Subsidiary.cPZdigest,Rd_Style.cRdName,isnull(Vendor.cVenName,Vendor.cVenabbName)cVenName,isnull(customer.ccusname,customer.ccusabbname)asccusname,IA_Subsidiary.cItem_class,IA_Subsidiary.cItemCode,IA_Subsidiary.cAccounter,strContractCode,cproordercode,iproorderids,cworkprocode,cworkcentername,csaleordercode,isaleorderids,IA_Subsidiary.exoCode,IA_Subsidiary.iExRowno,centrustordercode,cpurordercode,cIMOrdercode,IA_Subsidiary.cDefine1,IA_Subsidiary.cDefine2,IA_Subsidiary.cDefine3,IA_Subsidiary.cDefine4,IA_Subsidiary.cDefine5,IA_Subsidiary.cDefine6,IA_Subsidiary.cDefine7,IA_Subsidiary.cDefine8,IA_Subsidiary.cDefine9,IA_Subsidiary.cDefine10,IA_Subsidiary.cDefine11,IA_Subsidiary.cDefine12,IA_Subsidiary.cDefine13执行语句如下：,IA_Subsidiary.cDefine14,IA_Subsidiary.cDefine15,IA_Subsidiary.cDefine16,IA_Subsidiary.cDefine22,IA_Subsidiary.cDefine23,IA_Subsidiary.cDefine24,IA_Subsidiary.cDefine25,IA_Subsidiary.cDefine26,IA_Subsidiary.cDefine27,IA_Subsidiary.cDefine28,IA_Subsidiary.cDefine29,IA_Subsidiary.cDefine30,IA_Subsidiary.cDefine31,IA_Subsidiary.cDefine32,IA_Subsidiary.cDefine33,IA_Subsidiary.cDefine34,IA_Subsidiary.cDefine35,IA_Subsidiary.cDefine36,IA_Subsidiary.cDefine37,IA_Subsidiary.cFree1,IA_Subsidiary.cFree2,IA_Subsidiary.cFree3,IA_Subsidiary.cFree4,IA_Subsidiary.cFree5,IA_Subsidiary.cFree6,IA_Subsidiary.cFree7,IA_Subsidiary.cFree8,IA_Subsidiary.cFree9,IA_Subsidiary.cFree10,Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cInvDefine4,Inventory.cInvDefine5,Inventory.cInvDefine6,Inventory.cInvDefine7,Inventory.cInvDefine8,Inventory.cInvDefine9,Inventory.cInvDefine10,Inventory.cInvDefine11,Inventory.cInvDefine12,Inventory.cInvDefine13,Inventory.cInvDefine14,Inventory.cInvDefine15,Inventory.cInvDefine16,ltrim(Cast(caseIA_Subsidiary.bRdFlagwhen1thenIA_Subsidiary.iInCostelseIA_Subsidiary.iOutcostendasdecimal(20,2))),ltrim(Cast(ltrim(Cast(IA_Subsidiary.iAInQuantityasdecimal(20,2)))/cast(caseInventory.iGroupTypewhen1thenCU_F.iChangRateelseNULLendasdecimal(34,8))asdecimal(20,2)))asiAInQPiece,ltrim(Cast(IA_Subsidiary.iAInQuantityasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iInCostasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iAInPriceasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iAOutQuantity/cast(caseInventory.iGroupTypewhen1thenCU_F.iChangRateelseNULLendasdecimal(34,8))asdecimal(20,2)))asiAOutQPiece,ltrim(Cast(IA_Subsidiary.iAOutQuantityasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iOutCostasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iAOutPriceasdecimal(20,2))),'','','','',convert(varchar(30),IA_Subsidiary.iDebitDifCost),convert(varchar(30),IA_Subsidiary.iCreditDifCost),'',IA_Subsidiary.cVouType,IA_Subsidiary.AutoID,IA_Subsidiary.ID,IA_Subsidiary.bRdFlag,IA_Subsidiary.cPZID,IA_Subsidiary.iPZIDFROMIA_Subsidiarywith(nolock)LEFTJOINVendorwith(nolock)ONVendor.cVenCode=IA_Subsidiary.cVenCodeLEFTJOINRd_Stylewith(nolock)ONRd_Style.cRdCode=IA_Subsidiary.cRdCodeLEFTJOINInventorywith(nolock)ONInventory.cInvCode=IA_Subsidiary.cInvCodeleftjoinComputationUnitCU_Fwith(nolock)ONCU_F.cGroupCode=Inventory.cGroupCodeANDCU_F.bMainUnit=0andCU_F.iNumber=0LeftJoinWarehousewith(nolock)OnIA_Subsidiary.cWhCode=Warehouse.cWhCodeLeftJoinCustomerwith(nolock)ONIA_Subsidiary.cCusCode=customer.ccuscodeleftJOin(selectino_id,csign,coutno_idfromgl_accvouchwith(nolock)wherecOutsysName='IA'and(iflagisnulloriflag=2)GroupByino_id,csign,coutno_id)AONIA_Subsidiary.cPzID=A.coutno_idWhereIA_Subsidiary.dKeepDate<='2010-12-31'ANDia_subsidiary.imonth<>0ANDIA_Subsidiary.cWhCodeIn(N'01',N'02',N'04',N'05',N'08',N'10',N'30',N'31',N'32',N'33',N'34',N'35',N'36')andia_subsidiary.cinvcode=N'01019002065'ANDIA_Subsidiary.iMonth<=12ANDIA_Subsidiary.cVouType<>N'33'ANDIA_Subsidiary.cVouType<>N'34'ORDERBYIA_Subsidiary.dKeepDate,isnull(IA_Subsidiary.bakID,IA_Subsidiary.AutoID),IA_Subsidiary.AutoID请注意红色标注的语句2.分析语句：系统在查询存货明细账时和总账中的GL_ACCVOUCH表建立了一个外连接如下SELECTIA_Subsidiary.dKeepDate,…FROMIA_Subsidiary…leftJOin（selectino_id,csign,coutno_idfromgl_accvouchwith(nolock)wherecOutsysName='IA'and(iflagisnulloriflag=2)GroupByino_id,csign,coutno_id)AONIA_Subsidiary.cPzID=A.coutno_id…大家都知道，外连接LEFT就是指定在结果集中包含指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为所有的行之外，将另外一个表的输出列设为NULLNULL。。3.在后台执行该语句，并同时查询总账中对应凭证信息：发现总账中0028号凭证和0130号凭证的Coutno_id一致，都是2010IA0000000000048。但是在明细账中却只有一条记录CPZID=2010IA0000000000048.通过查询存货明细账的语句执行却显示两条记录。从上面的执行结果不难看出，因为在总账中coutno_id=2010IA0000000000048的有两个凭证号0028和0130，但是在存货明细账中CPZID=2010IA0000000000048显然只有凭证号0028，但是根据以上执行语句查询存货明细账时IA_Subsidiary和GL_ACCVOUCH使用了leftJoin外连接并且A_Subsidiary.cPzID=A.coutno_id，所以查询存货明细账出现重复记录。
