BOM树结构处理(最后一层的QTY给第一层)

数据为：

ID          QTY         PID

----------- ----------- -----------

1           0           2

2           0           3

3           30          4

 

10          0           11

11          23          12

 

20          15          21

 

 

结果为：

ID          QTY

----------- -----------

1           30

10          23

20          15

 

--树结构,把最后一层的QTY传给第一层.

 

 

-->生成测试数据表:@t

DECLARE @t TABLE(ID INT,QTY INT,PID INT)

INSERT INTO @t

SELECT 1,0,2 UNION ALL

SELECT 2,0,3 UNION ALL

SELECT 3,30,4 UNION ALL

SELECT 10,0,11 UNION ALL

SELECT 11,23,12 UNION ALL

SELECT 20,15,21

 

-->SQL查询如下：

;WITH t AS

(

    SELECT *, AID = ID

    FROM @t AS A

    WHERE NOT EXISTS(

              SELECT *

              FROM @t

              WHERE PID = A.ID

          )

    UNION ALL

    SELECT A.*, AID

    FROM @t AS A

    JOIN t AS B

        ON  A.ID = B.PID

)

SELECT AID AS ID, QTY

FROM t AS A

WHERE NOT EXISTS(

          SELECT *

          FROM t

          WHERE AID = A.AID

             AND ID>A.ID

      )

ORDER BY 1

 

/*

ID          QTY

----------- -----------

1           30

10          23

20          15

 

(3 行受影响)

*/