我有一个这样的文件系统:
C Drive - Docements - (empty)
- Music - Rock - a.mp3
- Jazz - SmoothJazz - b.mp3
- Photo - (empty)
D Drive - (empty)
每个文件或目录都有一个id和其父id。现在给定一个文件名或目录名,如何找到它的完整路径?
我的算法是:
Recursively (
if (parent id is not null)
find parent id in the table
)
您可以为此使用递归查询。假设表file
具有列id
、name
、parent
:
WITH RECURSIVE t AS
(SELECT id, name, parent, name as path
FROM file
WHERE id=3
UNION ALL
SELECT si.id,si.name,
si.parent,
si.name || '/' || sp.path as path
FROM file As si
INNER JOIN t AS sp
ON (si.id = sp.parent)
)
SELECT *
FROM t where parent is null
ORDER BY path
这将为您提供path
中包含id=3
的文件的完整路径。可能不是最有效的查询。