找不到祖先的 CTE 查詢

我有一張桌子

與內容

所以表 foo 的記錄可以用這張圖來表示。

當我執行一個應該使用 @starting_id=9 返回祖先的過程時

（在圖中用圓圈表示），我得到了這個結果

• 9

代替

• 9
• 3和
• 1

為什麼？

CREATE PROCEDURE [dbo].[GetParents]
   @starting_id int
AS
BEGIN
   WITH chainIDsUpwards AS
   (
       SELECT id, parent_id FROM foo WHERE id = @starting_id

       UNION ALL

       SELECT foo.id, foo.parent_id FROM foo
       JOIN chainIDsUpwards p ON p.id = foo.parent_id
   )

   SELECT id FROM chainIDsUpwards
END

小提琴https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=81be3d86dc7581eb60bc7af4c09077e4

我相信你在你的連接謂詞中犯了一個錯誤。嘗試：

WITH chainIDsUpwards AS
   (
       SELECT id, parent_id FROM foo WHERE id = @starting_id

       UNION ALL

       SELECT foo.id, foo.parent_id FROM foo
       JOIN chainIDsUpwards p ON p.parent_id = foo.id
   )

   SELECT id FROM chainIDsUpwards

這是一個常見的錯誤，我經常自己做。如果我從 CTE 開始，我發現最容易做對：

   SELECT foo.id, foo.parent_id 
   FROM chainIDsUpwards p
   JOIN foo p 

然後將 CTE 視為一個鍊錶，我需要在其中找到下一個元素：

       ON p.parent_id = foo.id

引用自：https://dba.stackexchange.com/questions/274233