没有CTE的分层查询

Question

由于VistaDB上缺少CTE/递归查询，我正试图制定一个具有一定深度的可行查询来查询PARENT/ID层次自引用表。我有几个想法（SQL来自Firebird，因为我们在服务器端使用它）：

1. 执行多个联接，如下所示：

   SELECT
       "A"."ID",
       "B"."ID",
       "C"."ID",
       "D"."ID"
   FROM "NAVIGATION" AS A
       LEFT JOIN "NAVIGATION" AS B ON (B.PARENT = A.ID)
       LEFT JOIN "NAVIGATION" AS C ON (C.PARENT = B.ID)
       LEFT JOIN "NAVIGATION" AS D ON (D.PARENT = C.ID)
   WHERE "A"."ID" = CHAR_TO_UUID('00000000-0000-0000-0000-000000000000');
   

   然后对A、B、C、D"ID"列进行COALESCE，并将其用作实际行的子查询或联接源，以获取所需的内容。然而，由于第一层的第一行可能会连接到其他几行，所以这不起作用——我需要的是：

   A       B       C      D
   0       NULL    NULL   NULL
   0       1       NULL   NULL
   0       1       2      NULL
   0       1       2      3
   0       1       2      4
   0       1       2      5
   

   相反，正如预期的那样，我得到了这个：

   A       B       C      D
   0       1       2      3
   0       1       2      4
   0       1       2      5
   

   有办法获得额外的NULL行吗？

2. 将UNION与子查询一起使用。然而，我想不出一个可行的语法来完成这项工作。

3. 也许是另一种语法。我们只需要几个层次的深度。从技术上讲，我们可以在应用程序中评估（1.）的结果，但我更喜欢一种更优雅的方法，尽管它不一定很快。我们通常只会在客户端深处查询两到三个级别，有时只查询一个级别。不过，如果不按程序行事，那就太好了。

根据要求提供一些样本数据：

ID    PARENT    TITLE
0     NULL      'Root Node'
1     0         '1st Level Node'
2     1         '2nd Level Node'
3     2         '3nd Level Node 1'
4     2         '3nd Level Node 2'
5     2         '3nd Level Node 3'

Answer 1

如果您有示例数据，它会有所帮助。但是，如果其他表中有匹配的行，则查询无法返回A/NULL/NULL/NULL。

获取所有层次结构的一种方法是为每个联接添加一个NULL值：

SELECT "A"."ID", "B"."ID", "C"."ID", "D"."ID"
FROM "NAVIGATION" AS A LEFT JOIN
     (SELECT N.PARENT, N.ID
      FROM "NAVIGATION"
      UNION ALL
      SELECT NULL, NULL
     ) B
     ON B.PARENT = A.ID
     (SELECT N.PARENT, N.ID
      FROM "NAVIGATION"
      UNION ALL
      SELECT NULL, NULL
     ) C
     ON C.PARENT = B.ID LEFT JOIN
     (SELECT N.PARENT, N.ID
      FROM "NAVIGATION"
      UNION ALL
      SELECT NULL, NULL
     ) D
     ON D.PARENT = C.ID
WHERE "A"."ID" = CHAR_TO_UUID('00000000-0000-0000-0000-000000000000');

Answer 2

以下是好奇者的完整代码。当然，它可以扩展到更多级别。

SELECT NAVIGATION.* FROM (
SELECT
    COALESCE("E"."ID", "D"."ID", "C"."ID", "B"."ID", "A"."ID") AS FINAL_ID
FROM "NAVIGATION" AS A LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS B 
ON ("B"."PARENT" = "A"."ID") OR ("B"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS C
ON ("C"."PARENT" = "B"."ID") OR ("C"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS D
ON ("D"."PARENT" = "C"."ID") OR ("D"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS E
ON ("E"."PARENT" = "D"."ID") OR ("E"."ID" IS NULL)
WHERE "A"."ID" = '00000000-0000-0000-0000-000000000000'
)
LEFT JOIN NAVIGATION ON NAVIGATION.ID = FINAL_ID;

Answer 3

通常临时表是CTE的良好替代品。事实上，为了提高性能，有时最好使用临时表而不是CTE，尤其是当您计划多次加入CTE时。

运行此

INSERT INTO #MyCTEReplacement
select ID, ParentID, 0 as Level
from Navigation
where ParentID is null

循环这个，直到你测量到0个受影响的记录-一个从SqlCommand.ExecuteNonQuery（）得到的返回int

INSERT INTO #MyCTEReplacement
select ID, ParentID, (X.Level + 1) as Level
from Navigation N
join #MyCTEReplacement X
    on X.ID = N.ParentID
where ParentID is null

您可能也可以在带有循环的存储过程中执行此操作。但至少您没有使用CURSOR（ew），并且您可以向临时表添加索引。

注意：这是上面的伪代码：不是完美的VistaDB语法，并且不完全匹配您的模式需要