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.

早在我在Oracle商店工作时，我就认为CONNECT_BY是理所当然的。现在，我正在使用SQL Server 2005，并且有一些令人讨厌的对象层次结构。具体地说，我们有一个自引用表，其中所有子记录都有一个列，列中有其父id。虽然这种方法很有效，但它并不优雅，而且带有污点。我只是好奇其他人如何从SQL Server 2005中检索分层数据。

6 个解决方案

#1

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

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

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

OUTPUT:

输出:

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

#2

Having used both, I found CONNECT BY is somewhat more flexible and easier to use than CTE's. The question is not dissimilar to one I answered a few weeks ago. See Here for a brief comparison of CONNECT BY and CTE's and Here for an example of a query using CTE's.

使用这两种方法后，我发现CONNECT BY比CTE更灵活、更容易使用。这个问题和我几个星期前回答的问题没有什么不同。请参阅这里，以简要比较CONNECT BY和CTE，这里是使用CTE的查询示例。

#3

Just FYI. SQL Server 2008 supports a new data type Hierarchy ID.

仅供参考。SQL Server 2008支持新的数据类型层次结构ID。

#4

Read this:

读:

http://www.sitepoint.com/article/hierarchical-data-database/2/

It should give you some ideas...

它会给你一些建议……

#5

in SQL Server 2005 you can use Common Table Expressions (CTE) for this.

在SQL Server 2005中，您可以为此使用公共表表达式(CTE)。

#6

To traverse the Depth of the Hierarchy first then the next sibling level, CTE can be used:

要先遍历层次结构的深度，然后再遍历下一个同级层，可以使用CTE:

declare @tempTable TABLE
(
    ORGUID int,
    ORGNAME nvarchar(100), 
    PARENTORGUID int,
    ORGPATH nvarchar(max)
)

;WITH RECORG(ORGuid, ORGNAME, PARENTORGUID, ORGPATH)
as
(
    select 
        org.UID,
        org.Name,
        org.ParentOrganizationUID,
        dbo.fGetOrganizationBreadcrumbs(org.UID)
    from Organization org
    where org.UID =1

    union all

    select 
        orgRec.UID,
        orgRec.Name,
        orgRec.ParentOrganizationUID,
        dbo.fGetOrganizationBreadcrumbs(orgRec.UID) 
    from Organization orgRec
    inner join RECORG recOrg on orgRec.ParentOrganizationUID = recOrg.ORGuid

)
insert into @tempTable(ORGUID, ORGNAME, PARENTORGUID,ORGPATH)

select ORGUID, ORGNAME, PARENTORGUID,ORGPATH 
from  RECORG rec 

select * 
from @tempTable where ORGUID in(select MIN(tt.ORGUID) 
                                from @tempTable tt 
                                group by tt.PARENTORGUID)

#1