usando - Consultas jerárquicas en SQL Server 2005

mysql arbol jerarquico

Question

Hace mucho tiempo, cuando trabajaba en una tienda de Oracle, daba por sentado el CONNECT_BY. Ahora estoy trabado trabajando con SQL Server 2005 y tengo algunas jerarquías de objetos desagradables. Específicamente, tenemos una tabla de autorreferencia en la que todos los registros secundarios tienen una columna con la identificación de sus padres. Actualmente tenemos una vista que asigna a los niños a niveles en la jerarquía y una desagradable consulta que hace el trabajo pesado para conectar a los padres con sus hijos. Si bien este método funciona, está lejos de ser elegante y huele a mancha. Solo tengo curiosidad de cómo otras personas recuperan datos jerárquicos de SQL Server 2005.

Answer 1

Esto crea su tabla jerárquica típica y utiliza un CTE para seleccionar la estructura jerárquica y crear una ruta para cada elemento.

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

SALIDA:

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

Answer 2

Habiendo usado ambos, encontré que CONNECT BY es algo más flexible y fácil de usar que los CTE. La pregunta no es diferente a una que respondí hace unas semanas. Vea aquí para una breve comparación de CONNECT BY y CTE y aquí para ver un ejemplo de una consulta que usa CTE.

Answer 3

Lee esto:

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

Debería darte algunas ideas ...

Answer 4

Para recorrer la profundidad de la jerarquía primero y luego el siguiente nivel de hermanos, se puede usar 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)

Answer 5

Solo para tu información. SQL Server 2008 admite una nueva identificación de jerarquía de tipo de datos.

Answer 6

en SQL Server 2005 puede usar Common Table Expressions (CTE) para esto.