miércoles, 11 de febrero de 2015

Consultas jerárquicas ordenadas (T-SQL)

Los datos jerárquicos usualmente se almacenan en tablas que contienen referencias recursivas como se muestra en la imagen:


Esta estructura permite almacenar datos jerárquicos, por ejemplo, como han derivado unas distribuciones GNU/Linux de otras.

Id Nombre IdPadre
1 Debian NULL
2 Knoppix 1
3 Ubuntu 1
4 Lindows 1
5 Corel 1
6 Damn Small Linux 2
7 KnoppMyth 2
8 Mint 3
9 Kubuntu 3
10 Xubuntu 3
11 Ulteo 9
12 Redhat NULL
13 Mandrake 12

Si queremos consultar todos los registros de distribuciones basadas en Debian, podemos recurrir a una consulta utilizando la sintaxis CTE (Common Table Expression, SQL Server 2005+) como sigue:

WITH cte
     AS (SELECT A.id, A.idpadre, A.nombre
         FROM   tabla1 A
         WHERE  A.id = 1 --Iniciar con Debian
         UNION ALL
         SELECT B.id, B.idpadre, B.nombre
         FROM   tabla1 B
                INNER JOIN cte C ON B.idpadre = C.id
         WHERE  B.idpadre IS NOT NULL)
SELECT *
FROM   cte 


Esta consulta devolverá los datos en el orden en que los va recuperando. Si quisiéramos que los datos aparezcan ordenados según la jerarquía, deberemos considerar una columna adicional en la consulta que corresponda a la ruta completa desde el registro raíz (Debian) hasta cada registro.

WITH cte
     AS (SELECT A.id, A.idpadre, A.nombre,
                Cast(A.id AS VARCHAR) ruta
         FROM   tabla1 A
         WHERE  A.id = 1 --Iniciar con Debian
         UNION ALL
         SELECT B.id, B.idpadre, B.nombre,
                Cast(C.ruta + '-' + Cast(B.id AS VARCHAR) AS VARCHAR)
         FROM   tabla1 B
                INNER JOIN cte C ON B.idpadre = C.id
         WHERE  B.idpadre IS NOT NULL)
SELECT *
FROM   cte
ORDER  BY ruta ASC; 


La columna Ruta actuará como un "acumulador" de todos los IDs que han de considerarse para llegar desde el registro raíz (Debian) hasta el registro destino.

En el ejemplo, la ruta correspondiente a Ulteo es 1-3-9-11.


No hay comentarios:

Publicar un comentario