我正在使用Firebird 2.1。有一个表名称Folders
,其中包含以下字段:
如果它是根文件夹,则
ParentFolderID
为-1;否则,它包含父文件夹的ID。如何找到低级别节点的所有父项(直到根文件夹)?
我需要递归查询吗? (Firebird supports them)
最佳答案
像这样的东西:
WITH RECURSIVE hierarchy (folderid, ParentFolderId, FolderName) as (
SELECT folderid, ParentFolderId, FolderName
FROM folders
WHERE ParentFolderID = -1
UNION ALL
SELECT folderid, ParentFolderId, FolderName
FROM folders f
JOIN hierarchy p ON p.folderID = f.parentFolderID
)
SELECT *
FROM hierarchy
编辑:以下查询将使层次结构“向上”移动,找到给定文件夹的所有父级。
WITH RECURSIVE hierarchy (folderid, ParentFolderId, FolderName) as (
SELECT folderid, ParentFolderId, FolderName
FROM folders
WHERE folderid = 42
UNION ALL
SELECT folderid, ParentFolderId, FolderName
FROM folders f
JOIN hierarchy p ON p.parentFolderID = f.folderID
)
SELECT *
FROM hierarchy
关于sql - 如何使用SQL在数据库中检测具有嵌套关系的父级?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6585631/