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,'文件1',0 union all select 2,'文件2',0 union all select 3,'文件1-1',1 union all select 4,'文件2-1',2 union all select 5,'文件1-1-1',3 union all select 6,'文件2-1-1',4 --SQL查询如下: ;WITH t AS ( SELECT groupID,foldName,parentID, path=CAST(foldName AS VARCHAR(8000)) FROM Temp AS A WHERE NOT EXISTS ( SELECT * FROM Temp WHERE groupID=A.parentID ) UNION ALL SELECT A.*, B.path+'/'+A.foldName FROM Temp AS A JOIN t AS B ON A.parentID=B.groupID ) SELECT groupID,path AS foldname FROM t order by groupID /* groupID path ------- --------------------------- 1 文件1 2 文件2 3 文件1/文件1-1 4 文件2/文件2-1 5 文件1/文件1-1/文件1-1-1 6 文件2/文件2-1/文件2-1-1 (6 行受影响) */