sql - SQL中的层次结构搜索

我有下表

ID   CATALOGUE  SWID   PARENTSWID   LEVEL
1       1         1         1         0
2       1         2         1         1
3       1         3         1         1
4       1         4         2         2
5       1         5         4         3
6       1         6         5         4
7       2         2         2         0
8       2         3         2         1

因此，我希望所有具有(SWID)的元素PARENTSWID= "1"。

根级别始终为“ 0”，并且根元素具有相同的SWID和PARENTSWID

树的深度没有边界。
稍后，我将不得不使用CATALOGUE属性来限制搜索，但是现在我需要一个如何设置它的想法。

那么，有人做过这样的事情吗？ :)

亲切的问候
森马

WITH cte as
(
SELECT ID, CATALOGUE, SWID, PARENTSWID, LEVEL
FROM table
WHERE PARENTSWID = 1
AND LEVEL = 0
UNION ALL
SELECT t.ID, t.CATALOGUE, t.SWID, t.PARENTSWID, t.LEVEL
FROM table t
JOIN CTE ON cte.ID = t.PARENTSWID
AND t.LEVEL= cte.LEVEL+1
)
SELECT ID, CATALOGUE, SWID, PARENTSWID, LEVEL FROM cte;

尝试此操作时，出现错误“未知的ISC错误336397226”。
我查了这个错误，它的意思是：336397226 dsql_cte_cycle CTE '@1' has cyclic dependencies

我的桌子和这里的桌子一样

（我在选择数据库的情况下在IBWSQL上进行了尝试）

最佳答案

这是我使用通用表表达式的Firebird SQL解决方案。

CREATE TABLE STACKOVERFLOW0001 (
    ID          INTEGER,
    CATALOUGE   INTEGER,
    SWID        INTEGER,
    PARENTSWID  INTEGER,
    "LEVEL"     INTEGER
);

INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (1, 1, 1, 1, 0);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (2, 1, 2, 1, 1);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (3, 1, 3, 1, 1);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (4, 1, 4, 2, 2);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (5, 1, 5, 4, 3);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (6, 1, 6, 5, 4);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (7, 2, 2, 2, 0);
INSERT INTO STACKOVERFLOW0001 (ID, CATALOUGE, SWID, PARENTSWID, "LEVEL")
                       VALUES (8, 2, 3, 2, 1);

COMMIT WORK;

Query

WITH RECURSIVE cat (id,catalouge,swid,parentswid,lvl)
as
(
    select id,catalouge,swid,parentswid,"LEVEL" from stackoverflow0001 as r
    where r."LEVEL" = 0 -- Magic Happens here for Filtering, you can specify the Start of the recursive tree walk down from here.


    union all

    select r.id, r.catalouge, r.swid, r.parentswid,r."LEVEL" from stackoverflow0001 as r,cat
    where cat.swid = r.parentswid -- This is what matches up the first query record to the child records
    and r."LEVEL" > 0 -- Just an additional filter check for child records.
)

select * from cat
// You can also stick some where clauses here if you need

Output

ID  CATALOUGE   SWID    PARENTSWID  LVL
1   1   1   1   0
2   1   2   1   1
4   1   4   2   2
5   1   5   4   3
6   1   6   5   4
8   2   3   2   1
3   1   3   1   1
7   2   2   2   0
4   1   4   2   2
5   1   5   4   3
6   1   6   5   4
8   2   3   2   1
8   2   3   2   1