如何制作一个sql循环？

here is the simplified table

这是简化表

filesystem (id, name, parentId);

and some entries

和一些条目

(1, 'root', NULL)
(2, 'folder', 1)
(3, 'subfolder', 2)
(4, 'subsubfolder', 3)

is there a way using native SQL to print the absolute path of one entry ?

有没有办法使用本机SQL打印一个条目的绝对路径?

for instance, the last entry would print 'root/folder/subfolder/subsubfolder'. the entry 2 would print 'root/folder' and so on.

例如,最后一个条目将打印'root / folder / subfolder / subsubfolder'。条目2将打印“root / folder”,依此类推。

3 个解决方案

#1

You can do something like this

你可以做这样的事情

 with tree(id, Level, Hierarchy) as
(
 select id, 0, cast(Name as varchar(max))
   from filesystem
 union all
 select a.id, b.Level+1,
   b.Hierarchy+'/'+a.Name
  from filesystem a
  inner join tree b on a.parentid=b.id
)
select top(1) id, Hierarchy
 from tree
 where id=4
 order by Level desc 

It will give you id with full file path. TO read in details you can check this

它将为您提供完整文件路径的ID。要详细阅读,您可以查看此信息

#2

You didn't state your DBMS, the following is standard (ANSI) SQL:

您没有说明您的DBMS,以下是标准(ANSI)SQL:

with recursive folder_tree as (
   select id, name, parentid, name as fullpath
   from filesystem
   where parentid is null
   union all 
   select c.id, c.name, c.parentid, p.fullpath||'/'||c.name 
   from filesystem c
      join folder_tree p on c.parentid = p.id
)
select *
from folder_tree

SQLFiddle: http://sqlfiddle.com/#!15/91332/7

#3

Recursive CTE solution for SQL Server:

WITH FileSystem(id,name,parentID)
AS
(
    SELECT 1,'root',NULL
    UNION ALL
    SELECT 2,'folder',1
    UNION ALL
    SELECT 3,'subFolder',2
    UNION ALL
    SELECT 4,'subSubFolder',3
),
CTE_Recursion
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY ID) filePath_id,ID,CAST(name AS VARCHAR(100)) name,parentID
    FROM FileSystem
    WHERE parentID IS NULL

    UNION ALL

    SELECT A.filePath_id,B.id,CAST(A.name + '\' +  B.name AS VARCHAR(100)),B.parentID
    FROM CTE_Recursion A
    INNER JOIN  FileSystem B
    ON A.ID = B.parentID
)

SELECT filePath_id,MAX(name) filePath
FROM CTE_Recursion
GROUP BY filepath_id

Results:

filePath_id          filePath
-------------------- -----------------------------------
1                    root\folder\subFolder\subSubFolder