递归 SQL 帮助

Question

我有一个与此类似的类别表：

   uid | ParentLevel | ParentID | Name
   ------------------------------------
    1  |       0     |      0   |  foo
    2  |       1     |      1   |  blat
    3  |       1     |      1   |  baz
    4  |       2     |      3   |  blah
    5  |       0     |      0   |  bar

我正在尝试获得与此类似的输出：

1 | foo
2 | foo | blat
3 | foo | baz
4 | foo | baz | blah
5 | bar

等等。 CTE 已经出来了，因为这是 SQL 2000。我尝试过类似的方法，但似乎不太正确：

SELECT     c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM         dbo.Categories AS c1 INNER JOIN
                  dbo.Categories AS c2 ON c1.uid = c2.ParentID INNER JOIN
                  dbo.Categories AS c3 ON c2.uid = c3.ParentID
WHERE     (c1.ParentLevel = 0) AND (c2.ParentLevel = 1) AND (c3.ParentLevel = 2)

只有在存在第三个级别（并不总是如此）时，才会返回记录。有办法做到这一点吗？

Answer 1

使用LEFT JOIN

SELECT     c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM         dbo.Categories AS c1 LEFT JOIN 
                  dbo.Categories AS c2 ON c1.uid = c2.ParentID LEFT JOIN 
                  dbo.Categories AS c3 ON c2.uid = c3.ParentID
WHERE     (c1.ParentLevel = 0) AND (c2.ParentLevel = 1) AND (c3.ParentLevel = 2)

Answer 2

使用 LEFT JOIN 而不是 INNER JOIN 并将级别上的条件从 WHERE 移动到 JOIN 条件

SELECT      c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM        dbo.Categories AS c1 
LEFT JOIN   dbo.Categories AS c2 
        ON  c1.uid = c2.ParentID
        AND c2.ParentLevel = 1
LEFT JOIN   dbo.Categories AS c3 
        ON  c2.uid = c3.ParentID
        AND c3.ParentLevel = 2
WHERE       c1.ParentLevel = 0

Answer 3

LEFT JOIN 将帮助您获得预期的结果。 INNER JOIN 仅合并与其他查询结果匹配的记录。