本文介绍了如何制作一个sql循环?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是简化表
filesystem (id, name, parentId);
和一些条目
(1, 'root', NULL)
(2, 'folder', 1)
(3, 'subfolder', 2)
(4, 'subsubfolder', 3)
有没有办法使用原生 SQL 打印一个条目的绝对路径?
is there a way using native SQL to print the absolute path of one entry ?
例如,最后一个条目将打印root/folder/subfolder/subsubfolder".条目 2 将打印根/文件夹"等.
for instance, the last entry would print 'root/folder/subfolder/subsubfolder'. the entry 2 would print 'root/folder' and so on.
推荐答案
您没有说明您的 DBMS,以下是标准 (ANSI) SQL:
You didn't state your DBMS, the following is standard (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
这篇关于如何制作一个sql循环?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!