请邹健、libin_ftsafe(子陌红尘：当libin告别ftsafe)等高手进来看一下这段SQL的写法，能不能再优化一下

DECLARE @Select varchar(8000)
    -- 货位入库信息
    SELECT @Select = 'SELECT  
                          VGoodsLocationC,
                          Vtrancode,
  VStartPoint,
                          V_OldGBGrade,
                          V_GBGrade,
                          SUM(IPackageCount) AS IPackageCount,
                          SUM(NWeight) AS NWeight
                      INTO    
                          ##storInTemp 
                      FROM 
                          V_InStorehouseInfo 
                      WHERE VGoodsLocationC 
IN (SELECT VGoodsLocationC FROM V_InStorehouseInfo '
                          + @Condition + ') 
or VGoodsLocationC in (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
                          + @MoveCondition + ')
                      GROUP BY  
                          VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade'     EXEC (@Select)
    -- 移库移入数 T_StorMoveSub
    SET @Select = '
    SELECT
        VGoodsLocationC_In,
        VTranCode,
V_ProducingArea,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        ##TempMoveInSub
    FROM 
        V_StorMoveInfo
    WHERE 
        V_CommitFlag = ''已经提交'' 
AND (VGoodsLocationC_In 
IN (SELECT VGoodsLocationC FROM ##storInTemp ) 
OR VGoodsLocationC_In 
IN (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
        + @MoveCondition + '))
    GROUP BY  
        VGoodsLocationC_In, VTranCode,V_ProducingArea,V_OldGBGrade,V_GBGrade'
    EXEC (@Select)    -- 加上移库移入数
    SET @Select = '
    INSERT INTO 
        ##storInTemp (
            VGoodsLocationC,
            Vtrancode,
    VStartPoint,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
        )
        SELECT 
            VGoodsLocationC_In,
            VTranCode,
    V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
        FROM 
            ##TempMoveInSub'
    EXEC(@Select)    -- 获取货位冲账信息
    SELECT 
        VGoodsLocationC,
        V_TranCode,
        V_ProducingArea,
        V_GBGradeOld,
        V_GBGrade,
        I_PackageCountBalance,
        N_WeightBalance
    INTO
#TempLocationStrikeBalance
    FROM 
        V_StorStrikeBalanceInfo
    WHERE 
VGoodsLocationC 
IN (SELECT VGoodsLocationC FROM ##storInTemp)
    
    -- 插入冲账数据进行计算
    INSERT INTO 
        ##storInTemp (
            VGoodsLocationC,
            Vtrancode,
            VStartPoint,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
        )
    SELECT 
            VGoodsLocationC,
            V_TranCode,
    V_ProducingArea,
            V_GBGradeOld,
            V_GBGrade,
            I_PackageCountBalance,
            N_WeightBalance
    FROM 
        #TempLocationStrikeBalance
    -- 入库 + 移库移入 + 冲账
    SELECT 
        VGoodsLocationC,
        Vtrancode,
VStartPoint,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        #TempLocationIn 
    FROM 
        ##storInTemp
    GROUP BY 
        VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade 
    -- 货位出库信息
    SELECT 
        VGoodsLocationC,
        Vtrancode,
VGoodsLocation,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO
        ##storOutTemp
    FROM 
        V_OutStorehouseInfo
    WHERE 
        VGoodsLocationC IN (SELECT VGoodsLocationC FROM ##storInTemp)
    GROUP BY  
        VGoodsLocationC,Vtrancode, VGoodsLocation, V_OldGBGrade,V_GBGrade    -- 移库移出数
    SELECT
        VGoodsLocationC_Out,
        VTranCode,
        V_ProducingArea,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        #TempMoveOutSub
    FROM 
        V_StorMoveInfo
    WHERE 
        VGoodsLocationC_Out 
IN (SELECT VGoodsLocationC FROM ##storInTemp)
        AND V_CommitFlag = '已经提交'
    GROUP BY  
        VGoodsLocationC_Out,Vtrancode,V_ProducingArea,V_OldGBGrade,V_GBGrade
    -- 加上移库移出数--
    INSERT INTO 
        ##storOutTemp (
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
        )
    SELECT 
            VGoodsLocationC_Out,
            Vtrancode,
    V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            IPackageCount,
            NWeight
    FROM 
        #TempMoveOutSub
    -- 出库 + 移库移出
    SELECT 
        VGoodsLocationC,
        Vtrancode,
VGoodsLocation,
        V_OldGBGrade,
        V_GBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO 
        #TempLocationOut
    FROM 
        ##storOutTemp
    GROUP BY  
        VGoodsLocationC,Vtrancode,VGoodsLocation,V_OldGBGrade,V_GBGrade    UPDATE 
        #TempLocationIn 
    SET 
        #TempLocationIn.IPackageCount = #TempLocationIn.IPackageCount - #TempLocationOut.IPackageCount,
        #TempLocationIn.NWeight = #TempLocationIn.NWeight - #TempLocationOut.NWeight 
    FROM 
        #TempLocationOut 
    WHERE 
        #TempLocationOut.[VGoodsLocationC] = #TempLocationIn.[VGoodsLocationC] AND 
        #TempLocationOut.[Vtrancode] = #TempLocationIn.[Vtrancode]  AND
        #TempLocationOut.[VGoodsLocation] = #TempLocationIn.[VStartPoint]  AND
        #TempLocationOut.[V_OldGBGrade] = #TempLocationIn.[V_OldGBGrade]  AND
        #TempLocationOut.[V_GBGrade] = #TempLocationIn.[V_GBGrade]  
  
    ---------------------------------------------------------------------------------------------------
    DELETE FROM #TempLocationIn WHERE IPackageCount = 0 AND NWeight = 0
    -- 获取发货地 年度 收购类型 入库日期 运单号 司磅员  
  
    SELECT 
        A.*,
        B.DTobaccoDate,
        B.VSBY, 
        B.VReserveType,
        B.DInDate
    INTO 
        ##LocationsMoveStorage
    FROM 
        #TempLocationIn A
    LEFT JOIN 
        T_StorRealIn B 
    ON 
        A.Vtrancode = B.Vtrancode 
    ORDER BY A.VGoodsLocationC    SELECT @Select = '
    SELECT 
        identity(int,1,1) AS OrderID,
        VGoodsLocationC,
        V_GBGrade,
        VStartPoint,
        CONVERT(CHAR(4),DTobaccoDate,112) AS DTobaccoDate,
        VReserveType,
        VSBY,
        DInDate,
        VTranCode,
        V_OldGBGrade,
        SUM(IPackageCount) AS IPackageCount,
        SUM(NWeight) AS NWeight
    INTO    
        ##LocationsStorageReturnOrder 
    FROM 
        ##LocationsMoveStorage'
    + @FinllyCondition +'
    GROUP BY 
        VGoodsLocationC,V_GBGrade,VStartPoint,DTobaccoDate,VReserveType,VSBY,DInDate,VTranCode,V_OldGBGrade'
    EXEC (@Select)    -- select * from ##LocationsStorageReturnOrder -- 最终初烟货位库存结果集

解决方案 »

  1.   

    to klan:
    都一个星了,也不帮忙看一下
      

  2.   

    CQP 你刚不是问了一个嘛`, =_= 不是同一个吧
      

  3.   

    我的天，好长的sql，我晕了，楼下的晕了吗？
      

  4.   

    没事,不客气的,CQP~  ,我晚上回去给你写吧,一看好象是逻辑有问题容易改的.
    如果邹、子陌他们经过就算了
      

  5.   

    V_InStorehouseInfo,V_StorMoveInfo,V_StorStrikeBalanceInfo,T_StorRealIn数据量请报一下，特别是第二个
      

  6.   

    select count(*) from V_InStorehouseInfo
    select count(*) from V_StorMoveInfo
    select count(*) from V_StorStrikeBalanceInfo
    select count(*) from T_StorRealIn33375、1006、22、11235
      

  7.   

    to zhuaiman(米米涨了(︶︿︶)╭∩╮) :
      多谢你了
      

  8.   

    --兄弟，对不起我来晚了--
    DECLARE @Select varchar(8000)
        -- 货位入库信息
        SELECT @Select = '
        SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              SUM(IPackageCount) AS IPackageCount,
                              SUM(NWeight) AS NWeight
        INTO    
                              ##storInTemp 
        FROM (
        SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              IPackageCount,
                              NWeight FROM V_InStorehouseInfo '
                              + @Condition + 'UNION ALL
         SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              IPackageCount,
                              NWeight FROM V_InStorehouseInfo 
        WHERE VGoodsLocationC in (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
                              + @MoveCondition + ')
    ) A
                          GROUP BY  
                              VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade' 
    --此处优化代码没减少，但是性能可以提升不少
    --原因V_InStorehouseInfo有30000多条数据如果有IN(子查询30000多条)的话会增加原基础上30%(我执行计划得到)开销
        EXEC (@Select)
        -- 移库移入数 T_StorMoveSub
        -- 加上移库移入数
        SET @Select = '    
        INSERT INTO ##storInTemp
        SELECT
            VGoodsLocationC_In,
            VTranCode,
    V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_StorMoveInfo
        WHERE 
            V_CommitFlag = ''已经提交'' 
    AND (VGoodsLocationC_In 
    IN (SELECT VGoodsLocationC FROM ##storInTemp ) 
    OR VGoodsLocationC_In 
    IN (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
            + @MoveCondition + '))
        GROUP BY  
            VGoodsLocationC_In, VTranCode,V_ProducingArea,V_OldGBGrade,V_GBGrade'
        EXEC (@Select)
        -- 获取货位冲账信息
        -- 插入冲账数据进行计算
        INSERT INTO ##storInTemp 
    (
                VGoodsLocationC,
                Vtrancode,
                VStartPoint,
                V_OldGBGrade,
                V_GBGrade,
                IPackageCount,
                NWeight
            )
        SELECT 
            VGoodsLocationC,
            V_TranCode,
            V_ProducingArea,
            V_GBGradeOld,
            V_GBGrade,
            I_PackageCountBalance,
            N_WeightBalance    FROM 
            V_StorStrikeBalanceInfo
        WHERE 
    VGoodsLocationC 
    IN (SELECT VGoodsLocationC FROM ##storInTemp)
            -- 入库 + 移库移入 + 冲账
        SELECT 
            VGoodsLocationC,
            Vtrancode,
    VStartPoint,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO 
            #TempLocationIn 
        FROM 
            ##storInTemp
        GROUP BY 
            VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade 
        -- 货位出库信息
          -- 出库 + 移库移出
        UPDATE 
            #TempLocationIn 
        SET 
            #TempLocationIn.IPackageCount = #TempLocationIn.IPackageCount - TempLocationOut.IPackageCount,
            #TempLocationIn.NWeight = #TempLocationIn.NWeight - TempLocationOut.NWeight 
        FROM (
    SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            (  SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_OutStorehouseInfo
        WHERE 
            VGoodsLocationC IN (SELECT VGoodsLocationC FROM ##storInTemp)
        GROUP BY  
            VGoodsLocationC,Vtrancode, VGoodsLocation, V_OldGBGrade,V_GBGrade
        UNION ALL
        SELECT
            VGoodsLocationC_Out,
            VTranCode,
            V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_StorMoveInfo
        WHERE 
            VGoodsLocationC_Out 
    IN (SELECT VGoodsLocationC FROM ##storInTemp)
            AND V_CommitFlag = '已经提交'
        GROUP BY  
            VGoodsLocationC_Out,Vtrancode,V_ProducingArea,V_OldGBGrade,V_GBGrade) AS storOutTemp
        GROUP BY  
            VGoodsLocationC,Vtrancode,VGoodsLocation,V_OldGBGrade,V_GBGrade) AS TempLocationOut 
        WHERE 
            TempLocationOut.[VGoodsLocationC] = #TempLocationIn.[VGoodsLocationC] AND 
            TempLocationOut.[Vtrancode] = #TempLocationIn.[Vtrancode]  AND
            TempLocationOut.[VGoodsLocation] = #TempLocationIn.[VStartPoint]  AND
            TempLocationOut.[V_OldGBGrade] = #TempLocationIn.[V_OldGBGrade]  AND
            TempLocationOut.[V_GBGrade] = #TempLocationIn.[V_GBGrade] 
      
        ---------------------------------------------------------------------------------------------------
        DELETE FROM #TempLocationIn WHERE IPackageCount = 0 AND NWeight = 0
        -- 获取发货地 年度 收购类型 入库日期 运单号 司磅员  
      
        SELECT @Select = '
        SELECT 
            identity(int,1,1) AS OrderID,
            VGoodsLocationC,
            V_GBGrade,
            VStartPoint,
            CONVERT(CHAR(4),DTobaccoDate,112) AS DTobaccoDate,
            VReserveType,
            VSBY,
            DInDate,
            VTranCode,
            V_OldGBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO    
            ##LocationsStorageReturnOrder 
        FROM 
            (SELECT 
            A.*,
            B.DTobaccoDate,
            B.VSBY, 
            B.VReserveType,
            B.DInDate
         FROM 
             #TempLocationIn A
         LEFT JOIN T_StorRealIn B 
         ON 
            A.Vtrancode = B.Vtrancode 
         ORDER BY A.VGoodsLocationC ) AS LocationsMoveStorage '
        + @FinllyCondition +'
        GROUP BY 
            VGoodsLocationC,V_GBGrade,VStartPoint,DTobaccoDate,VReserveType,VSBY,DInDate,VTranCode,V_OldGBGrade'
        EXEC (@Select)    -- select * from ##LocationsStorageReturnOrder -- 最终初烟货位库存结果集
      

  9.   

    你测试下，应该在4秒左右完成
    ---------------------------
    未完全优化
    ---------------------------
    无法完成优化任务的原因:
    1、我并不是很清楚你业务的逻辑，只是去大概的、估量的优化（特别是第一步，数据量稍微大一点的表）。
    2、无法完成优化的原因还在于子过程太多，每次建立Fiter和生成要DefaultView要花费时间，这还是小因素。
    3、最大因素在于贵公司同事的一些处理方法上有完全的偏向，不需要拿太多临时表去处理，交换数据完全可以通过变量或表变量，难道不考虑每一次I/O开销有多大吗？
    --------------------------------
    如果你觉得不妥，我会在明天晚上给你完全优化，今天仅仅优化了I/O和子查询。并没有优化业务处理逻辑，对不起了
      

  10.   

    UPDATE 
            #TempLocationIn 
        SET 
            #TempLocationIn.IPackageCount = #TempLocationIn.IPackageCount - TempLocationOut.IPackageCount,
            #TempLocationIn.NWeight = #TempLocationIn.NWeight - TempLocationOut.NWeight 
        FROM (
    SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            (  SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_OutStorehouseInfo
        WHERE 
            VGoodsLocationC IN (SELECT VGoodsLocationC FROM ##storInTemp)
        GROUP BY  
            VGoodsLocationC,Vtrancode, VGoodsLocation, V_OldGBGrade,V_GBGrade
        UNION ALL
        SELECT
            VGoodsLocationC_Out,
            VTranCode,
            V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_StorMoveInfo
        WHERE 
            VGoodsLocationC_Out 
    IN (SELECT VGoodsLocationC FROM ##storInTemp)
            AND V_CommitFlag = '已经提交'
        GROUP BY  
            VGoodsLocationC_Out,Vtrancode,V_ProducingArea,V_OldGBGrade,V_GBGrade) AS storOutTemp
        GROUP BY  
            VGoodsLocationC,Vtrancode,VGoodsLocation,V_OldGBGrade,V_GBGrade) AS TempLocationOut 
        WHERE 
            TempLocationOut.[VGoodsLocationC] = #TempLocationIn.[VGoodsLocationC] AND 
            TempLocationOut.[Vtrancode] = #TempLocationIn.[Vtrancode]  AND
            TempLocationOut.[VGoodsLocation] = #TempLocationIn.[VStartPoint]  AND
            TempLocationOut.[V_OldGBGrade] = #TempLocationIn.[V_OldGBGrade]  AND
            TempLocationOut.[V_GBGrade] = #TempLocationIn.[V_GBGrade] 这段是有问题的,执行时间太长了,还出错
      

  11.   

    请试试完全替换为以下语句
    DECLARE @Select varchar(8000)
        -- 货位入库信息
        SELECT @Select = '
        SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              SUM(IPackageCount) AS IPackageCount,
                              SUM(NWeight) AS NWeight
        INTO    
                              ##storInTemp 
        FROM (
        SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              IPackageCount,
                              NWeight FROM V_InStorehouseInfo '
                              + @Condition + 'UNION ALL
         SELECT                           
      VGoodsLocationC,
                              Vtrancode,
      VStartPoint,
                              V_OldGBGrade,
                              V_GBGrade,
                              IPackageCount,
                              NWeight FROM V_InStorehouseInfo 
        WHERE VGoodsLocationC in (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
                              + @MoveCondition + ')
    ) A
                          GROUP BY  
                              VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade' 
    --此处优化代码没减少，但是性能可以提升不少
    --原因V_InStorehouseInfo有30000多条数据如果有IN(子查询30000多条)的话会增加原基础上30%(我执行计划得到)开销
        EXEC (@Select)
        -- 移库移入数 T_StorMoveSub
        -- 加上移库移入数
        SET @Select = '    
        INSERT INTO ##storInTemp
        SELECT
            VGoodsLocationC_In,
            VTranCode,
    V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        FROM 
            V_StorMoveInfo
        WHERE 
            V_CommitFlag = ''已经提交'' 
    AND (VGoodsLocationC_In 
    IN (SELECT VGoodsLocationC FROM ##storInTemp ) 
    OR VGoodsLocationC_In 
    IN (SELECT VGoodsLocationC_In FROM V_StorMoveInfo '
            + @MoveCondition + '))
        GROUP BY  
            VGoodsLocationC_In, VTranCode,V_ProducingArea,V_OldGBGrade,V_GBGrade'
        EXEC (@Select)        -- 获取货位冲账信息
        SELECT 
            VGoodsLocationC,
            V_TranCode,
            V_ProducingArea,
            V_GBGradeOld,
            V_GBGrade,
            I_PackageCountBalance,
            N_WeightBalance
        INTO
    #TempLocationStrikeBalance
        FROM 
            V_StorStrikeBalanceInfo
        WHERE 
    VGoodsLocationC 
    IN (SELECT VGoodsLocationC FROM ##storInTemp)
        
        -- 插入冲账数据进行计算
        INSERT INTO 
            ##storInTemp (
                VGoodsLocationC,
                Vtrancode,
                VStartPoint,
                V_OldGBGrade,
                V_GBGrade,
                IPackageCount,
                NWeight
            )
        SELECT 
                VGoodsLocationC,
                V_TranCode,
        V_ProducingArea,
                V_GBGradeOld,
                V_GBGrade,
                I_PackageCountBalance,
                N_WeightBalance
        FROM 
            #TempLocationStrikeBalance
        -- 入库 + 移库移入 + 冲账
        SELECT 
            VGoodsLocationC,
            Vtrancode,
    VStartPoint,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO 
            #TempLocationIn 
        FROM 
            ##storInTemp
        GROUP BY 
            VGoodsLocationC,Vtrancode,VStartPoint,V_OldGBGrade,V_GBGrade 
        -- 货位出库信息
        SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO
            ##storOutTemp
        FROM 
            V_OutStorehouseInfo
        WHERE 
            VGoodsLocationC IN (SELECT VGoodsLocationC FROM ##storInTemp)
        GROUP BY  
            VGoodsLocationC,Vtrancode, VGoodsLocation, V_OldGBGrade,V_GBGrade    -- 移库移出数
        SELECT
            VGoodsLocationC_Out,
            VTranCode,
            V_ProducingArea,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO 
            #TempMoveOutSub
        FROM 
            V_StorMoveInfo
        WHERE 
            VGoodsLocationC_Out 
    IN (SELECT VGoodsLocationC FROM ##storInTemp)
            AND V_CommitFlag = '已经提交'
        GROUP BY  
            VGoodsLocationC_Out,Vtrancode,V_ProducingArea,V_OldGBGrade,V_GBGrade
        -- 加上移库移出数--
        INSERT INTO 
            ##storOutTemp (
                VGoodsLocationC,
                Vtrancode,
        VGoodsLocation,
                V_OldGBGrade,
                V_GBGrade,
                IPackageCount,
                NWeight
            )
        SELECT 
                VGoodsLocationC_Out,
                Vtrancode,
        V_ProducingArea,
                V_OldGBGrade,
                V_GBGrade,
                IPackageCount,
                NWeight
        FROM 
            #TempMoveOutSub
        -- 出库 + 移库移出
        SELECT 
            VGoodsLocationC,
            Vtrancode,
    VGoodsLocation,
            V_OldGBGrade,
            V_GBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO 
            #TempLocationOut
        FROM 
            ##storOutTemp
        GROUP BY  
            VGoodsLocationC,Vtrancode,VGoodsLocation,V_OldGBGrade,V_GBGrade    UPDATE 
            #TempLocationIn 
        SET 
            #TempLocationIn.IPackageCount = #TempLocationIn.IPackageCount - #TempLocationOut.IPackageCount,
            #TempLocationIn.NWeight = #TempLocationIn.NWeight - #TempLocationOut.NWeight 
        FROM 
            #TempLocationOut 
        WHERE 
            #TempLocationOut.[VGoodsLocationC] = #TempLocationIn.[VGoodsLocationC] AND 
            #TempLocationOut.[Vtrancode] = #TempLocationIn.[Vtrancode]  AND
            #TempLocationOut.[VGoodsLocation] = #TempLocationIn.[VStartPoint]  AND
            #TempLocationOut.[V_OldGBGrade] = #TempLocationIn.[V_OldGBGrade]  AND
            #TempLocationOut.[V_GBGrade] = #TempLocationIn.[V_GBGrade]  
      
        ---------------------------------------------------------------------------------------------------
        DELETE FROM #TempLocationIn WHERE IPackageCount = 0 AND NWeight = 0
        -- 获取发货地 年度 收购类型 入库日期 运单号 司磅员  
      
        SELECT 
            A.*,
            B.DTobaccoDate,
            B.VSBY, 
            B.VReserveType,
            B.DInDate
        INTO 
            ##LocationsMoveStorage
        FROM 
            #TempLocationIn A
        LEFT JOIN 
            T_StorRealIn B 
        ON 
            A.Vtrancode = B.Vtrancode 
        ORDER BY A.VGoodsLocationC    SELECT @Select = '
        SELECT 
            identity(int,1,1) AS OrderID,
            VGoodsLocationC,
            V_GBGrade,
            VStartPoint,
            CONVERT(CHAR(4),DTobaccoDate,112) AS DTobaccoDate,
            VReserveType,
            VSBY,
            DInDate,
            VTranCode,
            V_OldGBGrade,
            SUM(IPackageCount) AS IPackageCount,
            SUM(NWeight) AS NWeight
        INTO    
            ##LocationsStorageReturnOrder 
        FROM 
            ##LocationsMoveStorage'
        + @FinllyCondition +'
        GROUP BY 
            VGoodsLocationC,V_GBGrade,VStartPoint,DTobaccoDate,VReserveType,VSBY,DInDate,VTranCode,V_OldGBGrade'
        EXEC (@Select)    -- select * from ##LocationsStorageReturnOrder -- 最终初烟货位库存结果集