SQL Server 2005 中的分层查询 [英] Hierarchical Queries in SQL Server 2005

问题描述

当我在 Oracle 商店工作时，我认为 CONNECT_BY 是理所当然的.现在我被困在使用 SQL Server 2005 并且有一些讨厌的对象层次结构.具体来说，我们有一个自引用表，其中所有子记录都有一个带有其父 ID 的列.目前，我们有一个视图将孩子映射到层次结构中的级别，还有一个讨厌的查询，可以完成繁重的工作以将父母与孩子联系起来.虽然这种方法有效，但它远非优雅和臭味.我只是好奇其他人是如何从 SQL Server 2005 中检索分层数据的.

Way back when I was working in an Oracle shop I took the CONNECT_BY for granted. Now I'm stuck working with SQL Server 2005 and have some nasty object hierarchies. Specifically, we have a self referencing table where all child records have a column with their parent's id. Currently we have a view that maps children to levels in the hierarchy and a nasty query that does the heavy lifting to connect parents with their children. While this method works, it is far from elegant and reeks of taint. I'm just curious how other people retrieve hierarchical data from SQL Server 2005.

推荐答案

这将创建典型的分层表并使用 CTE 来选择分层结构并为每个项目创建路径.

This creates your typical hierarchical table and uses a CTE to select the hierarchy structure and create a path for each item.

CREATE TABLE tblHierarchy (ID int, ParentID int NULL, Name varchar(128));

INSERT INTO tblHierarchy VALUES (1, NULL, '1');
INSERT INTO tblHierarchy VALUES (2, NULL, '2');
INSERT INTO tblHierarchy VALUES (3, NULL, '3');
INSERT INTO tblHierarchy VALUES (4, 1, '1.1');
INSERT INTO tblHierarchy VALUES (5, 1, '1.2');
INSERT INTO tblHierarchy VALUES (6, 4, '1.1.1');

WITH Parent AS
(
    SELECT
        ID,
        ParentID,
        Name AS Path
    FROM
        tblHierarchy
    WHERE
        ParentID IS NULL

    UNION ALL

    SELECT
        TH.ID,
        TH.ParentID,
        CONVERT(varchar(128), Parent.Path + '/' + TH.Name) AS Path
    FROM
        tblHierarchy TH
    INNER JOIN
        Parent
    ON
        Parent.ID = TH.ParentID
)
SELECT * FROM Parent

输出:

ID  ParentID    Path
1   NULL        1
2   NULL        2
3   NULL        3
4   1       1/1.1
5   1       1/1.2
6   4       1/1.1/1.1.1

这篇关于SQL Server 2005 中的分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！