27,582
社区成员




WITH /* 测试数据
table1(DocNo,SrcDocNo,Qty) AS (
SELECT 'A',NULL,9 UNION ALL
SELECT 'B',NULL,8 UNION ALL
SELECT 'C',NULL,7 UNION ALL
SELECT 'D','A',1 UNION ALL
SELECT 'E','A',2 UNION ALL
SELECT 'F','B',3 UNION ALL
SELECT 'G','B',4 UNION ALL
SELECT 'H','D',5 UNION ALL
SELECT 'I','G',6 UNION ALL
SELECT 'J','F',0
), */
tree AS ( -- 递归求出Level、Path
SELECT *,
1 AS Level,
Convert(varchar(max),DocNo) AS Path
FROM table1
WHERE SrcDocNo IS NULL
UNION ALL
SELECT c.*,
p.Level + 1,
p.Path+'\'+c.DocNo
FROM tree p
JOIN table1 c
ON c.SrcDocNo = p.DocNo
)
-- 用Level缩进、Path排序
SELECT LEFT(SPACE(Level-1)+DocNo,4) DocNo,
SrcDocNo,
Qty
FROM tree
ORDER BY Path
DocNo SrcDocNo Qty
-------- -------- -----------
A NULL 9
D A 1
H D 5
E A 2
B NULL 8
F B 3
J F 0
G B 4
I G 6
C NULL 7
SELECT REPLICATE(' ',Qty)+DocNo AS[DocNo]
,SrcDocNo,Qty
FROM TB T
ORDER BY T.DocNo