sql - 如何正确创建递归查询？

Question

我有一张 table :

ItemID     ParentID             ItemName
  0          NULL                England
  50         0                   Hampshire county
  401        50                  Southampton
  402        50                  Portsmouth

我要的是下表:

ItemID     Level        ItemName                ItemPath
  0          0           England                 England
  50         1           Hampshire county        England\HampshireCounty
  401        2           Southampton             England\HampshireCounty\Southampton
  402        2           Portsmouth              England\HampshireCounty\Portsmouth

我创建了以下查询:

WITH tree (A_ItemName, A_ItemId, A_Level, pathstr)
AS (SELECT ItemName, ItemId, 0, 
         CAST('' AS VARCHAR(MAX)) 
     FROM Items 
     WHERE ParentID IS NULL 
     UNION ALL 
     SELECT ItemName, ItemID, t.A_Level + 1, t.pathstr + '/' + V.ItemName
     FROM Items V 
         INNER JOIN tree t 
             ON t.A_ItemId = V.ItemID) 
SELECT SPACE(A_Level) + A_ItemName as A_ItemName, A_ItemId, A_Level, pathstr 
FROM tree 
ORDER BY pathstr, A_ItemId

但是，我遇到了一个错误:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

知道我做错了什么吗？

Answer 1

您缺少父项。我认为这可以满足您的需求:

WITH tree (A_ItemName, A_ItemId, A_Level, pathstr) AS (
      SELECT ItemName, ItemId, 0, CAST(ItemName AS VARCHAR(MAX)) 
      FROM Items 
      WHERE ParentID IS NULL 
      UNION ALL 
      SELECT ItemName, ItemID, t.A_Level + 1, t.pathstr + '/' + V.ItemName
      FROM Items V INNER JOIN
           tree t 
           ON t.A_ItemId = V.ParentId
     ) 
SELECT SPACE(A_Level) + A_ItemName as A_ItemName, A_ItemId, A_Level, pathstr 
FROM tree 
ORDER BY pathstr, A_ItemId