用户对问题“rcte的递归成员中的cte”的回答

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

rcte的递归成员中的cte

DECLARE @x TABLE (xIdOf int,xIdLot int,xCoeff float);
    SELECT
        @pIdOf
        ,s.LOE_IdLot
        ,s.LOE_PdsS/(SELECT SUM(s1.LOE_PdsS) FROM dbo.REF_LOTS_STOCKOUT_OF AS s1 WHERE s1.LOE_IdOF=@pIdOf)
    FROM REF_LOTS_STOCKOUT_OF AS s
    WHERE s.LOE_IdOF=@pIdOf
    UNION ALL
    WITH cte_o(oIdOf, oIdLot, oPds, oCoeff) AS (
        SELECT s.LOE_IdOF,s.LOE_IdLot,AVG(s.LOE_PdsS),SUM(x.xCoeff)
            @x As x
            INNER JOIN REF_LOTS_STOCKIN_OF AS e
                ON x.xIdLot=e.LOS_IdLot
            INNER JOIN REF_LOTS_STOCKOUT_OF AS s
                ON e.LOS_IdOF=s.LOE_IdOF
        GROUP BY s.LOE_IdOF,s.LOE_IdLot
    SELECT
        o.oIdOf
        ,o.oIdLot
        ,o.oPds/(SELECT SUM(oS.oPds) FROM cte_o AS oS WHERE oS.oIdOf=o.oIdOf)*o.oCoeff
    FROM cte_o AS o;

DECLARE @x TABLE (xIdOf int,xIdLot int,xCoeff float);
DECLARE @o TABLE (oIdOf int,oIdLot int,oPds float,oCoeff float);
INSERT INTO @x
    SELECT
        @pIdOf
        ,s.LOE_IdLot
        ,s.LOE_PdsS/(SELECT SUM(s1.LOE_PdsS) FROM dbo.REF_LOTS_STOCKOUT_OF AS s1 WHERE s1.LOE_IdOF=@pIdOf)
    FROM REF_LOTS_STOCKOUT_OF AS s
    WHERE s.LOE_IdOF=@pIdOf;
WHILE EXISTS (SELECT x.xIdLot FROM @x As x)
BEGIN
    INSERT INTO @retGrapheOf
        SELECT
            @pIdOf
            ,x.xIdLot
            ,b.LBE_IdBE
            ,b.LBE_LgnBE
            ,x.xCoeff
        @x AS x
        INNER JOIN REF_LOTS_STOCKIN_BE AS b
            ON x.xIdLot=b.LBE_IdLot;
    INSERT INTO @o
        SELECT s.LOE_IdOF,s.LOE_IdLot,AVG(s.LOE_PdsS),SUM(x.xCoeff)
            @x As x
            INNER JOIN REF_LOTS_STOCKIN_OF AS e
                ON x.xIdLot=e.LOS_IdLot
            INNER JOIN REF_LOTS_STOCKOUT_OF AS s
                ON e.LOS_IdOF=s.LOE_IdOF
        GROUP BY s.LOE_IdOF,s.LOE_IdLot;
    DELETE FROM @x;
    INSERT INTO @x
        SELECT
            o.oIdOf
            ,o.oIdLot
            ,o.oPds/(SELECT SUM(oS.oPds) FROM @o AS oS WHERE oS.oIdOf=o.oIdOf)*o.oCoeff