主页 / 问题 / 1119465
Accepted
Daniel Dashkevich
Asked:2020-05-03 07:41:20 +0000 UTC2020-05-03 07:41:20 +0000 UTC

DB2 分层路径

  • 772

无法在云上的 ibm db2 中创建查询。首先尝试使用 SYS_CONNECT_BY_PATH 但显示意外令牌“PRIOR” 

SELECT ID0, MNAME, SYS_CONNECT_BY_PATH(MNAME, '/') AS path
FROM MAIN WHERE id0 IN (40,60) CONNECT BY NOCYCLE PRIOR id0=main_id;

我尝试像以前在 SQLite 中那样做，一切正常，但这里出现错误错误使用字符串列、主机变量、常量或函数“MNAME”

WITH paths (id, path) AS (SELECT id0, Mname FROM main WHERE main_id IS NULL 
UNION SELECT main.id0,  paths.path ||'/'|| main.Mname FROM main inner join paths on main.main_id = paths.id) 
SELECT a.id0, path FROM main AS a INNER JOIN paths AS b ON a.id0 = b.id where a.id0 in (40,60);

求助，怎么办？

база-данных

1 个回答

  1. Best Answer

    好像你想通了

    WITH paths (id, path, counter) AS
(  SELECT id0, Mname, 1 
   FROM main
   WHERE main_id IS NULL 
UNION all
   SELECT id0, path || '/' || Mname, counter + 1
   FROM main, paths
   WHERE main_id = id and counter <=100
) 
SELECT id, path
FROM paths
ORDER BY id
    • 0

相关问题