SQL2005递归(完整显示子树路径)

 

OBJECT_ID('[temp]') IS NOT NULL

GO

CREATE(

)

[temp]

1, '文件,SELECT' 0 UNION ALL

3, '文件,SELECT-1' 2 UNION ALL

5, '文件,SELECT-1-1' 4

--SQL查询如下; t AS

    SELECT groupID, foldName, parentID, PATH = CAST(foldName AS VARCHAR(MAX))

    WHERE NOT EXISTS

(

              FROM Temp

          )

    SELECT A.*, CAST(B.path+'/'+A.foldName AS VARCHAR(MAX))

    JOIN t AS B

)

groupID, PATH AS foldname

t

BY groupID

/*

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

/文件4           文件-1

/文件-1-1

/文件-1-1

(6 行受影响*/