SQL2005递归

IF OBJECT_ID('[temp]') IS NOT NULL

    DROP TABLE [temp]

GO

 

CREATE TABLE [temp]

(

    [groupID] INT, [foldName] VARCHAR(10), [parentID] INT

)

INSERT [temp]

SELECT 1, '文件', 0 UNION ALL

SELECT 2, '文件', 0 UNION ALL

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

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

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

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

 

--SQL查询如下:

;WITH t AS

(

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

    FROM Temp AS A

    WHERE NOT EXISTS

          (

              SELECT *

              FROM Temp

              WHERE groupID = A.parentID

          )

    UNION ALL

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

    FROM Temp AS A

    JOIN t AS B

        ON  A.parentID = B.groupID

)

SELECT groupID, PATH AS foldname

FROM t

ORDER BY groupID

 

 

结果....

1 文件
2 文件
3 文件/文件-1
4 文件/文件-1
5 文件/文件-1/文件-1-1
6 文件/文件-1/文件-1-1