我有一張桌子。

所以我們有一個分層對象結構，其中 foo 有一個父級（或沒有）和一個子級列表。

我想要的是一個 sql 語句，它列出了可以在該層次結構中向上和向下遍歷的所有 id:s。

如何？

我目前的嘗試是

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

       UNION ALL

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

       UNION ALL

       SELECT foo.id, foo.parent_id FROM foo
       JOIN chainIDsDownwards c ON foo.id = c.parent_id
   )

   SELECT * FROM chainIDsUpwards WHERE id = @starting_id
   UNION
   SELECT * FROM chainIDsDownwards WHERE id = @starting_id
END

但它確實只給了我一行。

編輯

樣本數據：

執行

$$ GetChain $$@starting_id = 5 返回

但我希望：

• 5, 2
• 2, 1
• 1、空
• 11, 5

小提琴在https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=aeaf40c57b3940ce725dccf553a8d9e2

編輯 2

我以為我找到了正確的 sql

CREATE PROCEDURE [dbo].[GetChain]
   @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
   ),
   chainIDsDownwards AS
   (
       SELECT id, parent_id FROM foo WHERE parent_id = @starting_id

       UNION ALL

       SELECT foo.id, foo.parent_id FROM foo
       JOIN chainIDsDownwards c ON foo.id = c.parent_id
   ),
   chainIDs AS
   (
       SELECT id FROM chainIDsUpwards
       UNION
       SELECT id FROM chainIDsDownwards
   )

   SELECT id FROM chainIDs
END

但這不會為 9 找到任何父母。它只找到 9 本身，而不是 3 或 1。

小提琴在https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=10041653e78b52bc8ec0e3c40cfff7a0

基本上，您需要將 @starting_id 謂詞移動到遞歸部分的基本情況：

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 foo.id = p.parent_id
)
SELECT * FROM chainIDsUpwards;

如果你把謂詞寫成：

SELECT * FROM chainIDsUpwards WHERE ...;

它將過濾所有不滿足謂詞的行。

我還認為，如果您希望行為與名稱相對應，則需要更改兩個 cte:s 中的 join 子句。我發現從聯接中的 cte 開始在精神上更容易，例如：

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

小提琴

只是為了搞笑。

我的偏好不是有兩個子查詢（一個用於祖先，一個用於後代），而是在單個語句中映射整個層次結構，並根據該語句在相關節點的上方和下方搜尋。

給定一個額外的計算列，將無父（或自父）實體標記為“ is_root= 1”，對於表單的層次結構……

1
|\
2 3
 / \
7   4

5
 \
  6

…我的範常式式碼是…

CREATE PROCEDURE [dbo].[GetChain]
   @starting_id int
AS
BEGIN
-- https://dba.stackexchange.com/q/273695/68127
   WITH cte AS (
       SELECT 
           id, 
           parent_id,
           depth = 0,
           '/'+convert(varchar(max),id) as _path
       FROM foo
       WHERE is_root = 1
       UNION ALL
       SELECT 
           foo.id, 
           foo.parent_id,
           depth + 1,
           _path + '/'+convert(varchar(max),foo.id)
       FROM cte
       JOIN foo ON foo.parent_id = cte.id
       where cte.depth < 100 -- i can never remember the maxrecursion hint syntax
           and foo.is_root = 0
   )
   SELECT * 
   FROM cte 
   where exists ( -- children (and self)
       select 1
       from string_split(_path,'/') ss
       where ss.value = @starting_id
   ) or cte.id in ( -- parents (and self)
       select ss.[value]
       from cte b
       cross apply string_split(b._path,'/') ss
       where b.id = @starting_id
   )
   order by _path
END

…這給了我們…

exec getChain 3;
/*
we expect the following nodes

1
 \
  3
 / \
7   4

2 is excluded because it is a sibling of 3, 
but not an ancestor or descendant
*/

完整的數據庫<>小提琴

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