代码之家 › 专栏 › 技术社区 › Stefan Steiger

SQL:如何从递归查询创建视图?

view recursion sql-server-2008 sql-server sql

Stefan Steiger · 技术社区 · 14 年前

问题:我有一个视图,我想从递归查询中派生出来。

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

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

如何创建这样的视图:

;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

1 回复 | 直到 14 年前

Joe Stefanelli 14 年前

应该简单到:

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

推荐文章

cristopher donaldo · SQL创建包含sum和2个表的视图

2 年前

Bálint Bakos · laravel在无刀片的情况下查看实体信息

2 年前

Mukul Kumar · 在snowflake中的特定视图上授予使用权限

2 年前

Vitzal · 使用Alert JavaFX更改场景

2 年前

rodrigo.rodriguez · 如何将控制器的参数传递给。aspx视图?

6 年前

DJhon · 使用不同的文本样式绘制自定义视图或按钮

6 年前

Luiz · 视图-索引SQL SERVEr

6 年前

Theo · 无法将TextInputText转换为TextInputLayout

6 年前

user5911925 · Laravel刀片:模板未渲染

6 年前

Mertcan Özdemir · JavaFX combobox正在调用listcell。无限更新项

6 年前