sql-server - SQL Server - 面包屑类型效果

Question

我有一组数据，如下所示:

ID, Description, ParentID
1,  Savoury, -1
2,  Cheese, 1
3,  Pastry, 1
4,  Quiche, 1
5,  Sweet, -1
6,  Chocolate, 5
...

我想要做的是编写一个存储过程，它会产生一组类似于 Web 浏览器中的面包屑链接的结果。像下面这样的东西将是理想的......

ID, BreadCrumb
1, Savoury
2, Savoury >> Cheese
3, Savoury >> Pastry
4, Savoury >> Quiche
5, Sweet
6, Sweet >> Chocolate
...

奶酪或糕点等物品不会单独列为物品(就像 Sweet 一样)。到目前为止，我有以下代码可以工作，但它列出了所有内容，无论它是否有 ParentID

With BreadCrumb AS 
(
SELECT CAST(a.Description AS VARCHAR(100)) AS Path, a.ID, a.ParentID
FROM FoodStuff a

UNION ALL

SELECT CAST(BreadCrumb.Path + ' >> ' + b.Description AS VARCHAR(100)) AS Path, b.ID, b.ParentID
FROM FoodStuff b
INNER JOIN BreadCrumb ON BreadCrumb.ID = b.ParentID

)

SELECT * FROM BreadCrumb
ORDER BY Path

我会很感激朝正确的方向插入。

提前致谢，
凯夫

Answer 1

您需要在递归 CTE 的 anchor 部分设置一个条件，以将其限制为根项目:

With BreadCrumb AS 
(
SELECT CAST(a.Description AS VARCHAR(100)) AS Path, a.ID, a.ParentID
FROM FoodStuff a
WHERE a.ParentID = -1 -- select roots only

UNION ALL

SELECT CAST(BreadCrumb.Path + ' >> ' + b.Description AS VARCHAR(100)) AS Path, b.ID, b.ParentID
FROM FoodStuff b
INNER JOIN BreadCrumb ON BreadCrumb.ID = b.ParentID
)

您不需要在 CTE 的递归一半中使用类似的条件，因为内部联接应该处理它，因为大概您没有任何 ID = -1 的记录。

除了最佳实践之外，我建议对允许没有值的外键(例如 ParentID)使用 NULL 而不是 -1。

SQL Fiddle ，由@Conrad Frix 提供。