你好   本脚本修复以下数据问题： 核算维度V表存在核算维度组合相同但FID不同的记录，导致科目余额表等报表核算维度有相同的记录不合并的问题 注意事项： 1，备份正式账套，并恢复成测试账套 2，在测试账套上执行修复操作，检查数据修复成功，且凭证、科目余额表等数据正确后再在正式账套上执行 3，查看未过账余额数据时需要在凭证查询的菜单中执行“刷新未过账余额表” 按以下步骤执行： 0，修改脚本中的日期（2017-09-01）为发现问题的期间的第一天（脚本只处理此日期及以后的凭证，大大节省时间） 1，所有账簿反结账反过账到发现问题前一个期间。 2，执行脚本以修复总账凭证及业务凭证表的数据。 3，重新过账以修复科目余额表、数据余额表以及损益余额表的数据 4，检查数据修复情况。 */ DECLARE @glvid int,@bizvid int; SELECT @glvid = MIN(FVOUCHERID) FROM T_GL_Voucher WHERE FCREATEDATE>=2017-09-01; SELECT @bizvid = MIN(FVOUCHERID) FROM T_BAS_Voucher WHERE FCREATEDATE>=2017-09-01; --备份指定日期以后通过凭证生成新产生的凭证分录数据及核算维度V表 IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(NT_GL_VOUCHERENTRY_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 1) SELECT * INTO T_GL_VOUCHERENTRY_20180101 FROM T_GL_VOUCHERENTRY WHERE FVOUCHERID>=@glvid  AND FVOUCHERID IN(   SELECT DISTINCT     FVOUCHERID   FROM    T_GL_VOUCHER V   JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY   ); IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(NT_BAS_VOUCHERENTRY_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 1) SELECT * INTO T_BAS_VOUCHERENTRY_20180101 FROM T_BAS_VOUCHERENTRY WHERE FVOUCHERID>=@bizvid  AND FVOUCHERID IN(   SELECT DISTINCT     FVOUCHERID   FROM    T_BAS_VOUCHER V   JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY   ); IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(NT_BD_FLEXITEMDETAILV_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 1) SELECT * INTO T_BD_FLEXITEMDETAILV_20180101 FROM T_BD_FLEXITEMDETAILV; --删除唯一索引 IF EXISTS(SELECT * FROM SYSINDEXES WHERE ID=OBJECT_ID(T_BD_FLEXITEMDETAILV) AND NAME=IDX_FLEXITEMUNIQUE) DROP INDEX IDX_FLEXITEMUNIQUE ON T_BD_FLEXITEMDETAILV; --开始批处理 BEGIN    DECLARE @flexfield VARCHAR(50) ,        @FCALCOL VARCHAR(2000) ,        @flexid1 INT ,        @flexid2 INT; --更新辅助资料核算维度为空的值为默认的空格    DECLARE flex_cursore CURSOR    FOR        ( SELECT    FFLEXNUMBER          FROM      T_BD_FLEXITEMPROPERTY          WHERE     FVALUETYPE = 1                    AND FDOCUMENTSTATUS = C        );    OPEN flex_cursore;    FETCH NEXT FROM flex_cursore INTO @flexfield;    WHILE @@FETCH_STATUS = 0        BEGIN            EXEC( UPDATE  T_BD_FLEXITEMDETAILV SET + @flexfield+ = WHERE +@flexfield+ = AND LEN(+@flexfield+)<=1);            FETCH NEXT FROM flex_cursore INTO @flexfield;        END;    CLOSE flex_cursore;    DEALLOCATE flex_cursore; DECLARE @cnt INT; SELECT @cnt = COUNT(*)     FROM      ( SELECT    MIN(FID) fid1 ,         MAX(FID) fid2 ,         FCALCOL        FROM      T_BD_FLEXITEMDETAILV        GROUP BY  FCALCOL      ) TM     WHERE     fid1 <> fid2; WHILE @cnt>0 BEGIN  --找出所有重复的核算维度，更新凭证等表中的维度组合ID为最小的那个，且保留FID值最小的那个，删除此后生成的重复维度ID  DECLARE flexid_cursore CURSOR  FOR   ( SELECT    fid1 ,      fid2 ,      FCALCOL     FROM      ( SELECT    MIN(FID) fid1 ,         MAX(FID) fid2 ,         FCALCOL        FROM      T_BD_FLEXITEMDETAILV        GROUP BY  FCALCOL      ) TM     WHERE     fid1 <> fid2   );  OPEN flexid_cursore;  FETCH NEXT FROM flexid_cursore INTO @flexid1, @flexid2, @FCALCOL;  WHILE @@FETCH_STATUS = 0   BEGIN    UPDATE  T_GL_VOUCHERENTRY    SET     FDETAILID = @flexid1    WHERE   FDETAILID = @flexid2      AND FVOUCHERID>=@glvid      AND FVOUCHERID IN(       SELECT DISTINCT         FVOUCHERID       FROM    T_GL_VOUCHER V         --JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY       );    UPDATE  T_BAS_VOUCHERENTRY    SET     FDETAILID = @flexid1    WHERE   FDETAILID = @flexid2      AND FVOUCHERID>=@bizvid      AND FVOUCHERID IN(       SELECT DISTINCT         FVOUCHERID       FROM    T_BAS_VOUCHER V         --JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY       );        DELETE  T_BD_FLEXITEMDETAILV    WHERE   FID > @flexid1      AND FCALCOL = @FCALCOL      AND FID NOT IN (        SELECT DISTINCT         FDETAILID        FROM   T_GL_VOUCHERENTRY        UNION        SELECT DISTINCT         FDETAILID        FROM   T_BAS_VOUCHERENTRY );    FETCH NEXT FROM flexid_cursore INTO @flexid1, @flexid2, @FCALCOL;   END;  CLOSE flexid_cursore;  DEALLOCATE flexid_cursore;  --检查是否还存在重复的ID  SELECT @cnt = COUNT(*)     FROM      ( SELECT    MIN(FID) fid1 ,         MAX(FID) fid2 ,         FCALCOL        FROM      T_BD_FLEXITEMDETAILV        GROUP BY  FCALCOL      ) TM     WHERE     fid1 <> fid2; END; END; GO --重新创建唯一索引 CREATE UNIQUE NONCLUSTERED INDEX IDX_FLEXITEMUNIQUE ON T_BD_FLEXITEMDETAILV (FCALCOL); GO