用H2 DB和SQL递归路径

发布于 2025-01-29 13:47:41 字数 572 浏览 1 评论 0原文

我有以下常见形式的路径名（路径深度不限）：

/a/a/b/c/d/e/...

示例

/a/b/c/d/e

预期结果

我现在想实现的目标是将路径拆分为包含文件夹和各个父的表

parent	：
/ C/D/	E
/A/B/C/	D
/A/B/	C
/A/	B
/	A

在分裂字符串方面，H2 DB的功能受到限制，因此我的假设是必须解决的。递归（尤其是因为路径深度不受限制）。

任何帮助将不胜感激:)

原文

I've path names of the following common form (path depth not limited):

/a/b/c/d/e/...

Example

/a/b/c/d/e

Expected result

What I'd like to achieve now is to split the path into a table containing the folder and the respective parent:

parent	folder
/a/b/c/d/	e
/a/b/c/	d
/a/b/	c
/a/	b
/	a

The capabilities of the H2 db are a bit limited when it comes to splitting strings, thus my assumption was it must be solved recursively (especially since the path depth is not limited).

Any help would be appreciated :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧城空念 2025-02-05 13:47:41

您需要使用递归查询，例如：

WITH RECURSIVE CTE(S, F, T) AS (
SELECT '/a/b/c/d/e', 0, 1
UNION ALL
SELECT S, T, LOCATE('/', S, T + 1)
FROM CTE
WHERE T <> 0
)
SELECT
    SUBSTRING(S FROM 1 FOR F) PARENT,
    SUBSTRING(S FROM F + 1 FOR
        CASE T WHEN 0 THEN CHARACTER_LENGTH(S) ELSE T - F - 1 END) FOLDER
FROM CTE WHERE F > 0;

它生成

父	文件夹
/	a
/a/	a
/a/a/	a
/a/a/a/b/c/	d
/a/a/a/b/c/d/	e

You need to use a recursive query, for example:

WITH RECURSIVE CTE(S, F, T) AS (
SELECT '/a/b/c/d/e', 0, 1
UNION ALL
SELECT S, T, LOCATE('/', S, T + 1)
FROM CTE
WHERE T <> 0
)
SELECT
    SUBSTRING(S FROM 1 FOR F) PARENT,
    SUBSTRING(S FROM F + 1 FOR
        CASE T WHEN 0 THEN CHARACTER_LENGTH(S) ELSE T - F - 1 END) FOLDER
FROM CTE WHERE F > 0;

It produces

PARENT	FOLDER
/	a
/a/	b
/a/b/	c
/a/b/c/	d
/a/b/c/d/	e

回复收藏 0 原文

记忆之渊 2025-02-05 13:47:41

做类似的事情：

with recursive 
  p(p) as (select '/a/b/c/d/e' as p),
  t(path, parent, folder, i) as (
    select 
      p, 
      REGEXP_REPLACE(p, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(p, '.*/(\w+)', '$1'), 
      1 
    from p
    union
    select 
      t.parent, 
      REGEXP_REPLACE(t.parent, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(t.parent, '.*/(\w+)', '$1'), 
      t.i + 1
    from t
    where t.parent != ''
  )
select *
from t;

导致您

|PATH      |PARENT  |FOLDER|I  |
|----------|--------|------|---|
|/a/b/c/d/e|/a/b/c/d|e     |1  |
|/a/b/c/d  |/a/b/c  |d     |2  |
|/a/b/c    |/a/b    |c     |3  |
|/a/b      |/a      |b     |4  |
|/a        |        |a     |5  |

不确定您是否对Tailt /字符真的感兴趣，但是您可以根据需要轻松修复查询。

Do something like this:

with recursive 
  p(p) as (select '/a/b/c/d/e' as p),
  t(path, parent, folder, i) as (
    select 
      p, 
      REGEXP_REPLACE(p, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(p, '.*/(\w+)', '$1'), 
      1 
    from p
    union
    select 
      t.parent, 
      REGEXP_REPLACE(t.parent, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(t.parent, '.*/(\w+)', '$1'), 
      t.i + 1
    from t
    where t.parent != ''
  )
select *
from t;

resulting in

|PATH      |PARENT  |FOLDER|I  |
|----------|--------|------|---|
|/a/b/c/d/e|/a/b/c/d|e     |1  |
|/a/b/c/d  |/a/b/c  |d     |2  |
|/a/b/c    |/a/b    |c     |3  |
|/a/b      |/a      |b     |4  |
|/a        |        |a     |5  |

Not sure if you're really interested in trailing / characters, but you can easily fix the query according to your needs.

回复收藏 0 原文

~没有更多了~