在子行的分層記錄集中包含父節點 ID

我有一個由 parent_category_id 列連結在一起的類別表。在此查詢中，我能夠辨識指定父級的所有子類別（可能是多個級別的子級），但我還想在每一行中包含父 ID。

WITH level_1 (category_id, category_name) AS (
 SELECT
  category_id, 
  category_name
 FROM category
 WHERE 
  parent_category_id = 1
 UNION ALL
 SELECT 
   c.category_id,
   c.category_name
 FROM category AS c CROSS JOIN level_1 
 WHERE 
   c.parent_category_id = level_1.category_id
)
select * from level_1

所寫的查詢返回以下內容：

category_id | category_name
        64 | Cows
        6  | Pigs
        11 | Holstein
        23 | Bantu

我想要的是下面，因為我想對這些父類別進行分組，但我不一定知道它們是什麼，除非它們有一個 parent_category_id = 1。

root_id | category_id |  category_name
     64 |          64 |  Cows
      6 |           6 |  Pigs
     64 |          11 |  Holstein
      6 |          23 |  Bantu

類別表如下所示。

  category_id         |  unique row identifier
  category_name       |  varchar
  category_parent_id  |  foreign key to category table 

我能夠解決這個問題的唯一方法是利用多個自連接的聯合而不是遞歸 CTE。當然，這只適用於有限數量的嵌套，但在我的情況下，這沒問題。

SELECT DISTINCT 
 c1.category_id AS category_id , 
 c1.category_name AS root_name,
 c1.category_id AS root_id 
FROM 
 category AS c0
 INNER JOIN category AS c1 ON c0.category_id = c1.parent_category_id
 WHERE c0.parent_category_id IS NULL

UNION ALL

SELECT DISTINCT 
 c2.category_id AS category_id, 
 c1.category_name AS root_name,
 c1.category_id AS root_id 
FROM category AS c0
 INNER JOIN category AS c1 ON c0.category_id = c1.parent_category_id
 INNER JOIN category AS c2 ON c1.category_id = c2.parent_category_id
WHERE c0.parent_category_id IS NULL

...

上面顯示了 1 層嵌套。對於每個後續級別，添加先前塊的副本並插入另一個內部連接：

Select
 Cn.category_id as category_id,
...
 inner join Cn ON c(n-1).category_id = Cn.parent_category_id
...

如果沒有看到以下定義，就很難理解您的要求category- 大概，該表中有一個root_id列？

如果有，也許你想要這個？

;WITH level_1 (category_id, category_name, root_id) AS (
   SELECT category_id, 
       category_name,
       root_id
   FROM dbo.category
   WHERE parent_category_id = 1
   UNION ALL
   SELECT c.category_id,
       category_name,
       root_id
   FROM dbo.category AS c 
   CROSS JOIN level_1 
   WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
   , category_name
   , root_id 
FROM level_1;

僅供參考，最好以分號開始 CTE ，指定 schema並避免使用SELECT *

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