sql - 如何从 SQL Server 中基于父 id 的分层表中获取数据

Question

我有一个分层表类别，如下所示，root_id父 ID 在哪里。

这里的意思root_id是表的主键（这里是 ID）。

category1、category2、category2_ge属于questionnare1。

subcategory1, subcategory_ge1, subcategory2, subcategory_ge2 属于 category1

subcategory3 属于 category2，subcategory4 属于 category2_ge

ID        name       root_id    level_id    description   language_id
-----------------------------------------------------------------------
1   questionnaire       0           1               desc1           1
2   category1           1           2               desc1           1
3   subcategory1        2           3               desc1           1
4   subcategory_ge1     2           3               desc1           2
5   subcategory2        2           3               desc2           1       
6   subcategory_ge2     2           3               desc2           2
7   category2           1           2               desc2           1
8   category2_ge        1           2               desc2           2
9   subcategory3        7           3               desc3           1
10  subcategory4        8           3               desc4           1

创建和插入查询如下，我想使用 FOR JSON 获取如下数据

CREATE TABLE categories 
(
   ID int,
   name varchar(255),
   root_id int,
   level_id int,
   description nvarchar(max),
   language_id int
);

INSERT INTO categories (ID, name, root_id, level_id, description, language_id)
VALUES (1, 'questionnaire', 0, 1, 'desc1', 1);
VALUES (2, 'category1', 1, 2, 'desc1', 1);
VALUES (3, 'subcategory1', 2, 3, 'desc1', 1);
VALUES (4, 'subcategory_ge1', 2, 3, 'desc1', 2);
VALUES (5, 'subcategory2', 2, 3, 'desc2', 1);
VALUES (6, 'subcategory_ge2', 2, 3, 'desc2', 2);
VALUES (7, 'category2', 1, 2, 'desc2', 1);
VALUES (8, 'category2_ge', 1, 2, 'desc2', 2);
VALUES (9, 'subcategory3', 7, 3, 'desc3', 1);
VALUES (10, 'subcategory4', 8, 3, 'desc4', 1);

所需数据如下

ID          name        root_id     level_id    questionnare_data
-------------------------------------------------------------------
1      questionnaire        1           0       {"questionnaire":{"ID":1,"name":"questionnaire", "level_id":1},"categories":{{"ID:2,"name":"category1","level_id":2,"subcategories":{{"ID":3,"name":"subcategory1","level_id":3},{"ID":4,"name":"subcategory_ge1","level_id":3},{"ID":5,"name":"subcategory2","level_id":3},{"ID":6,"name":"subcategory_ge2","level_id":3}}},{"ID":7",name":"category2","level_id":2,"subcategories":{{"ID":9,"name":"subcategory3","level_id":3}}},{"ID":8",name":"category2_ge","level_id":2,"subcategories":{{"ID":10,"name":"subcategory4","level_id":3}}}}}

为了简化我需要的 JSON，我已经简化如下

{"questionnaire":{"ID":1,"name":"questionnaire", "level_id":1},
                "categories":{
                        {"ID:2,"name":"category1","level_id":2,
                        "subcategories":{
                            {"ID":3,"name":"subcategory1","level_id":3},
                            {"ID":4,"name":"subcategory_ge1","level_id":3},
                            {"ID":5,"name":"subcategory2","level_id":3},
                            {"ID":6,"name":"subcategory_ge2","level_id":3}
                        }
                        },
                        {"ID":7",name":"category2","level_id":2,
                        "subcategories":{
                            {"ID":9,"name":"subcategory3","level_id":3}
                        }
                        },
                        {"ID":8",name":"category2_ge","level_id":2,
                        "subcategories":{
                            {"ID":10,"name":"subcategory4","level_id":3}
                        }
                        }
                }
}

我如何实现这一目标？

我的 SQL 版本是 Microsoft SQL Server 2016

我尝试使用在Select all hierarchy level and below SQL Server中找到的解决方案，但这是一个不同的场景

标签： sqlsql-serverstored-proceduressql-server-2016