SQL получить все категории и все подкатегории

Question

Можете ли вы помочь мне получить все категории со всеми подобными подкатегориями?

Сценарий создания базы данных

CREATE TABLE Category(Id INT NOT NULL, ParentId INT NULL)
GO
INSERT INTO Category (Id, ParentId) VALUES (1, NULL)
INSERT INTO Category (Id, ParentId) VALUES (2, 1)
INSERT INTO Category (Id, ParentId) VALUES (3, 1)
INSERT INTO Category (Id, ParentId) VALUES (4, 2)
INSERT INTO Category (Id, ParentId) VALUES (5, 2)
INSERT INTO Category (Id, ParentId) VALUES (6, 2)
GO

Я попытался в ms-sql решить эту проблему с помощью техники курсора и метода соединения, но смог получить все данные.

SELECT parent.Id AS ParentId, parent.ParentId AS ChildId FROM Category parent
JOIN Category child ON parent.Id = child.Id

Ожидая результата

ParentId    ChildId
----------- -----------
1           1
1           2
1           3
1           4
1           5
1           6
2           2
2           4
2           5
2           6
3           3
4           4
5           5
6           6

Благодарю вас

Answer 1

WITH RCTE AS 
(
    SELECT * , Id AS TopLevelParent
    FROM dbo.Category c

    UNION ALL

    SELECT c.* , r.TopLevelParent
    FROM dbo.Category c
    INNER JOIN RCTE r ON c.ParentId = r.Id
)
SELECT 
  r.TopLevelParent AS ParentID
, r.Id AS ChildID 
FROM RCTE r
ORDER BY ParentID;

SQLFiddle DEMO

Answer 2

Как уже указывалось, для этого вам нужен рекурсивный CTE. Одна вещь, которая делает это немного хитрее, это тот факт, что вы, по сути, преобразовывает ID (int) в символьные данные (-).

Я полагаю, что приведенный ниже запрос поможет вам достичь желаемых результатов. Это не принимает во внимание, что вы хотите, по сути, перечислить все дочерние элементы, независимо от их места в иерархии.

WITH CategoryTree AS
(
    SELECT 
        ID AS CategoryID
        , CAST(ID AS varchar(50)) AS CatVar
        , 0 AS LeafLevel    
        , ParentID
    FROM Category WHERE ParentID IS NULL

    UNION ALL

    SELECT 
        ID AS CategoryID 
        , CAST(CONCAT(CAST(Category.ParentID AS varchar), '-', CAST(Id AS varchar)) AS varchar(50)) AS CatVar
        , CategoryTree.LeafLevel + 1 AS LeafLevel   
        , Category.ParentID
    FROM Category 
        JOIN CategoryTree ON Category.ParentId = CategoryTree.CategoryId
    WHERE Category.ParentID IS NOT NULL
)
SELECT * FROM CategoryTree

Выход:

CategoryID  CatVar  LeafLevel   ParentID
1           1       0           NULL
2           1-2     1           1
3           1-3     1           1
4           2-4     2           2
5           2-5     2           2
6           2-6     2           2