22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T
(
ID INT,
上级ID int
)
INSERT INTO #T(ID,上级ID) VALUES(1,null),(2,null), (3,null),(4,1),(5,1),(6,2),(7,4),(8,6),(9,6),(10,6),(11,10)
;
WITH ct
AS
(
SELECT id,上级ID,CONVERT(VARCHAR(max),id) AS 编号 FROM #T WHERE 上级ID IS NULL
UNION ALL
SELECT B.id,B.上级ID,编号+'_'+CONVERT(VARCHAR(10),B .id) FROM ct A INNER JOIN #T B ON A.id=B.上级ID
)
SELECT * FROM ct
ORDER BY id
DROP TABLE #T