数据为:
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 行受影响)
*/