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.