sqlite递归查询，返回给定路径的id

我将文件夹的层次结构存储在sqlite数据库中，如下所示：

+----------+---------+----------+
| ParentID | ChildID |   Name   |
+----------+---------+----------+
|        1 |       2 | Folder A |
|        2 |       3 | Folder B |
|        3 |       4 | Folder C |
+----------+---------+----------+

哪里 Folder A 是根，结构是： Folder A/Folder B/Folder C .
目前，我有一个递归的cte，它可以根据给定的文件夹id派生文件夹的路径（它的工作原理是在childid列中找到id，然后向上遍历）
但是，我目前没有一种方法可以根据文件夹的路径导出其id。这绝对是有点棘手，因为我必须分裂的字符串上 / 如果我想查询表。
到目前为止，我已经开始递归cte：

SELECT Path, ChildID FROM DirectoryStructure, (
    SELECT SUBSTR('Folder A/Folder B/Folder C', 0, Position) as Root, 
           SUBSTR('Folder A/Folder B/Folder C', Position+1) as Path 
    FROM (SELECT INSTR('Folder A/Folder B/Folder C', '/') as Position)
) WHERE Name = Root

这将返回：

+-------------------+---------+
|       Path        | ChildID |
+-------------------+---------+
| Folder B/Folder C |       2 |
+-------------------+---------+

这是完美的，因为我有 Folder A 还有下一块要处理的路径。现在应该是递归地执行下一步的问题了 Folder B （和我一样 Folder A )在中找到条目 DirectoryStructure 哪里 ParentID = 2 AND Name = 'Folder B' 为了得到它 ChildID 等等。
然而，这正是我遇到麻烦的地方。我想我需要这样的东西：

WITH RECURSIVE GetId(Path, LastChild) AS (
    SELECT Path, ChildID FROM DirectoryStructure, (
        SELECT SUBSTR('Folder A/Folder B/Folder C', 0, Position) as Root, 
               SUBSTR('Folder A/Folder B/Folder C', Position+1) as Path 
        FROM (SELECT INSTR('Folder A/Folder B/Folder C', '/') as Position)
    ) WHERE Name = Root
    UNION ALL
    SELECT Path, ChildID FROM DirectoryStructure, (
        SELECT SUBSTR(GetId.Path, 0, Position) as Root, 
               SUBSTR(GetId.Path, Position+1) as Path
        FROM GetId, (SELECT INSTR(GetId.Path, '/') as Position FROM GetId)
    ) WHERE (ParentID = GetId.LastChild, Name = Root)
) SELECT * from GetId;

玩这个我通常会得到错误： Result: recursive reference in a subquery: GetId 我知道它不想我用 GetId 在子查询中，但我似乎找不出任何其他方法来实现这一点。
任何帮助都将不胜感激！

另一种方法是找到每个孩子的路径，然后与搜索路径进行比较：

WITH RECURSIVE CTE AS (
  SELECT ChildId, Name AS Path
  FROM DirectoryStructure
  WHERE ParentID = 1
  UNION ALL
  SELECT d.ChildId, Path || '/' || Name
  FROM DirectoryStructure d
  JOIN CTE ON d.ParentId = CTE.ChildId
)
SELECT *
FROM CTE
WHERE Path = 'Folder A/Folder B/Folder C'

输出：

ChildId     Path
4           Folder A/Folder B/Folder C

在dbfiddle上演示
如果您的表很大，而上面的查询太慢，则可以在执行时修剪条目，以确保它们与达到该级别的所需路径匹配：

WITH RECURSIVE CTE AS (
  SELECT ChildId, Name AS Path
  FROM DirectoryStructure
  WHERE ParentID = 1
    AND 'Folder A/Folder B/Folder C' LIKE Name || '%'
  UNION ALL
  SELECT d.ChildId, Path || '/' || Name
  FROM DirectoryStructure d
  JOIN CTE ON d.ParentId = CTE.ChildId
  WHERE 'Folder A/Folder B/Folder C' LIKE Path || '/' || Name || '%'
)
SELECT *
FROM CTE
WHERE Path = 'Folder A/Folder B/Folder C'

在dbfiddle上演示

sqlite递归查询，返回给定路径的id

1条答案

相关问题

热门标签

最新问答