SQL:如何从递归查询创建 View ？

Question

问题:我有一个想要从递归查询中派生的 View 。

该查询的结构与此处的查询结构相同: http://forums.asp.net/t/1207101.aspx

并将 TreeView 表示为有序数据集。

如何创建执行此操作的 View :

;WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS
(
    SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort FROM Category
    WHERE PARENT_ID = 0
    UNION ALL
    SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth, 
    CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort
    FROM Category CT
    INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID
)

-- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path
SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM Tree
ORDER BY Sort

Answer 1

它应该像这样简单:

CREATE VIEW YourViewName
AS
    WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS
    (
        SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort         
        FROM Category
        WHERE PARENT_ID = 0
        UNION ALL
        SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth, 
        CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort
        FROM Category CT
        INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID
    )

    -- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path
    SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM Tree
GO