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

DECLARE @table TABLE(ID INT,QTY INT,PID INT)
INSERT INTO @table
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

SELECT * FROM @table

结果为
ID QTY
----------- -----------
1 30
10 23
20 15

--树结构,但是没有规律,只要取最后一层的QTY给第一层就可以了.

--小梁

DECLARE @table TABLE(ID INT,QTY INT,PID INT)
INSERT INTO @table
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

;WITH Liang AS
(
SELECT *,flag=ID
FROM @table AS A
WHERE NOT EXISTS(SELECT * FROM @table WHERE PID = A.ID)
UNION ALL
SELECT A.*,B.flag FROM @table AS A
JOIN Liang AS B
ON A.ID = B.PID
)
SELECT A.flag AS ID,QTY FROM Liang AS A
WHERE NOT EXISTS(SELECT * FROM Liang WHERE flag=A.flag AND ID > A.ID)
ORDER BY 1

/*
ID QTY
----------- -----------
1 30
10 23
20 15

(3 行受影响)
*/