如何使用SQL在数据库中检测具有嵌套关系的父级? [英] How can you detect a parent with a nested relationship in a database using SQL?
本文介绍了如何使用SQL在数据库中检测具有嵌套关系的父级?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用Firebird 2.1.表名称为Folders
,其字段为:
I'm using Firebird 2.1. There is a table name Folders
, with the fields:
- 文件夹ID
- ParentFolderID
- FolderName
ParentFolderID
是根文件夹,则为-1-否则包含父文件夹的ID.
ParentFolderID
is -1 if it's the root folder -- otherwise it contains the parent folder's ID.
如何找到低级别节点的所有父节点(直到根文件夹)?
How can I find all parents (up to the root folder) of a low level node?
我需要递归查询吗? ( Firebird支持它们)
Do I need a recursive query? (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
编辑:以下查询将使层次结构向上"移动,找到给定文件夹的所有父项.
Edit: the following query will walk the hierarchy "up", finding all parents of a given folder.
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在数据库中检测具有嵌套关系的父级?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文