post icon

Realizar Consultas Recursivas en SQL Server

Como todo buen diseñador de base de datos sabe, es bastante común encontrarse con entidades recursivas en el diseño de nuestra BD, 2 ejemplos típicos son el jefe y el subordinado, en el diseño ambas personas se encuentran registradas como tuplas dentro de la entidad Persona o Funcionario (según el diseño que hemos tomado, incluso estaría mejor diseñado si se lo hace en base al cargo), al no existir 2 entidades que tengan cardinalidad 1:M, por que así obtendríamos duplicación de datos, debemos determinar un modo que ambos estén en la misma entidad y a su vez tener la capacidad de controlar quién es jefe de quién, esto se lograría agregando una columna más que sea del mismo dominio que su propia PK, es decir, la columna nueva sería FK de la PK que le determina, logrando así una cardinalidad 1:M recursiva.

Otro ejemplo típico es el caso de los contratos, estos suelen tener la característica que vencen en una fecha determinada, por cuestiones de ventas/marketing al cliente se le facilita normalmente este proceso con una renovación de contrato (en algunos casos automáticas), entonces el nuevo contrato debe poder determinarse lo siguiente: la renovación de que contrato está siendo, o si es el primer contrato, para realizarlo se aplica el mismo ejemplo anterior, una columna FK que sea del mismo dominio de la PK que le determina.

Pues llevemos esto a la práctica, aquí les dejo un script que genera una tabla, e inserta datos en ella, también verán que la sentencia WITH que es la que usaremos para hacer de manera sencilla la consulta recursiva. Tomaremos el ejemplo más típico de los jefes y subordinados :D

Muchos que ya tienen experiencia en este campo me dirán, ¿y por qué no usas simplemente un cursor para lograrlo?, la respuesta es sencilla, el motor ejecuta de manera más eficiente y con menos consumo de recursos del servidor que usando dicho cursor. Las tablas en este ejemplo no están completamente normalizadas como se darán cuenta, ya que nuestro objetivo no es ese :P 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- Estructura de la tabla recursiva (el campo cargo lo deje en varchar sin FK
-- para que sea mas sencilla la comprensión y corto el ejemplo)
 
-- aquí deberíamos hacer uso de alguna de nuestras base de datos
CREATE TABLE Empleado (NroEmpleado int,
	Nombre varchar(25),
	Apellido varchar(25),
	Cargo varchar(25),
	NroJefe int,
	CONSTRAINT PK_EMPLEADO PRIMARY KEY (NroEmpleado),
	CONSTRAINT FK_JEFE FOREIGN KEY (NroJefe) REFERENCES Empleado)
 
-- Datos de Ejemplo con instruccion INSERT
INSERT INTO Empleado VALUES (1, 'Carlos', 'Mendoza', 'Presidente', NULL)
INSERT INTO Empleado VALUES (2, 'Marta', 'Prieto', 'Vice Presidente', 1)
INSERT INTO Empleado VALUES (3, 'Victor', 'Mongelos', 'Gerente General', 1)
INSERT INTO Empleado VALUES (4, 'Betty', 'Martinez', 'Gerente RRHH', 3)
INSERT INTO Empleado VALUES (5, 'Marcos', 'Arce', 'Gerente Informatico', 3)
INSERT INTO Empleado VALUES (6, 'Hugo', 'Zorrilla', 'Gerente de Ventas', 3)
INSERT INTO Empleado VALUES (7, 'Marcela', 'Britoz', 'Gerente Comercial', 3)
INSERT INTO Empleado VALUES (8, 'Carlota', 'Muñoz', 'Gerente de Operaciones', 3)
INSERT INTO Empleado VALUES (9, 'Ricardo', 'Gallas', 'Atención al Cliente', 4)
INSERT INTO Empleado VALUES (10, 'Mafalda', 'Quiñonez', 'Auxiliar Contable', 7)
INSERT INTO Empleado VALUES (11, 'Cristian', 'Leite', 'Telemaketer', 4)
INSERT INTO Empleado VALUES (12, 'Osvaldo', 'Acuña', 'Team Leader', 8)
INSERT INTO Empleado VALUES (13, 'Consuelo', 'Vargas', 'Supervisor', 6)
INSERT INTO Empleado VALUES (14, 'Mateo', 'Dusrte', 'Supervisor de Seguridad', 4)
INSERT INTO Empleado VALUES (15, 'Miguel', 'Gllvan', 'Guardia de Seguridad', 14)
INSERT INTO Empleado VALUES (16, 'Carlos', 'Troche', 'Vendedor', 6)
INSERT INTO Empleado VALUES (17, 'Martina', 'Vera', 'Vendedor', 6)
INSERT INTO Empleado VALUES (18, 'Jose', 'Troche', 'Vendedor', 6)
INSERT INTO Empleado VALUES (19, 'Bruno', 'Zarza', 'Jefe de Marketing', 7)
INSERT INTO Empleado VALUES (20, 'Ana', 'Carreras', 'Auxiliar Contable', 7)
 
GO
--Consulta Recursiva para conocer los subordinados de un empleado en el organigrama hasta el mas bajo en la piramide
 
WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe)
AS
(
	SELECT NroEmpleado, Nombre, Apellido, Cargo, NroJefe
		FROM Empleado
		WHERE NroEmpleado = 4
	UNION ALL
--RECURSIVIDAD
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe
		FROM Empleado AS e JOIN EmpCTE AS m
			ON e.NroJefe = m.NroEmpleado
)
-- Desplegar ResultSet
SELECT * FROM EmpCTE
 
--Consulta Recursiva para conocer todos los jefes de un subordinado, hasta el jefe más alto en el organigrama
GO
WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe)
AS
(
	SELECT NroEmpleado, Nombre, Apellido, Cargo, NroJefe
		FROM Empleado
		WHERE NroEmpleado = 14
	UNION ALL
--RECURSIVIDAD
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe
		FROM Empleado AS e JOIN EmpCTE AS m
			ON e.NroEmpleado = m.NroJefe
)
-- Desplegar ResultSet
SELECT * FROM EmpCTE

Articulos Relacionados:

26 diciembre 2009

7 Comentarios

Deja tu Comentario
  1. DjMiki Google Chrome Windows
    2 marzo 2011 at 14:56 #

    Hola que tal GeekZero bueno chekee tu ejemplo y bueno lo había visto en una forma mas complicada en el Technet, pero sabes trato de hacer eso pero no me funciona siempre me sale que la consulta a sobrepasado el limite, ahora otra pregunta seria, en vez de mostrar el id del jefe, por q no mostrar el nombre del jefe, y si se puede como seria?

    Responder
    • GeekZero PARAGUAY Google Chrome Windows
      2 marzo 2011 at 16:00 #

      Si te da ese error es porque has puesto una sentencia where que no produce un paro. revisalo.

      Con respecto de mostrar el nombre del jefe puedes hacerlo asi:

      1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

      WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe, Jefe)
AS
(
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe, (select Nombre from empleado where NroEmpleado = e.NroJefe)
		FROM Empleado e
		WHERE NroEmpleado = 14
	UNION ALL
--RECURSIVIDAD
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe, (select Nombre from empleado where NroEmpleado = e.NroJefe)
		FROM Empleado AS e JOIN EmpCTE AS m
			ON e.NroEmpleado = m.NroJefe
			--and mNroEmpleado = e.NroJefe
)
-- Desplegar ResultSet
SELECT * FROM EmpCTE
      Responder
      • DjMiki Google Chrome Windows
        2 marzo 2011 at 16:43 #

        Muchas Gracias geekZero, me sirvió, pero sabes probe el codigo funciono bien, pero como quizás no ubiese querido relizar algo asi como recursivas, probe esto.
        SELECT C.nomCat,C.descCat,(SELECT nomCat FROM Categorias where idCat=C.catSup) FROM Categorias C

        Y eso me dio el mismo resultado, que la estructura de la consulta recursiva, pero mi pregunta es; Es igual?, genera algún retrazo en el motor de BD y por consecuencia cual debería ser el ideal..

        Responder
        • GeekZero PARAGUAY Google Chrome Windows
          2 marzo 2011 at 16:52 #

          Eso que has escrito no es recursividad, lo único que hace es mostrarte quien es su categoria padre, pero no genera un arbol.

          En el query recursivo podrás notar que si seleccionas al gerente verás todos los que estan debajo de su cargo, y quien esta debajo de su primer subordinado, quien es el subordinado de ese subordinado. Si eliges por ejemplo el Gte de Ventas verás abajo al Jefe de Ventas, luego al Sub-Jefe de Ventas luego a los vendedores por así decirlo. Y nunca verás al jefe de informatica por ejemplo. En cambio con el query que pusiste si verás a todos o si filtras verás menos gente pero no en escala jerarquica. Se comprende la diferencia?

          Si el query que has puesto satisfizo tu necesidad, no estabas buscando recursividad.

          Obviamente existen un par de técnicas más para crear recursividad, pero ésta es la más eficiente sobre SQL Server ya que está diseñado y optimizado para esa tarea.

          Saludos.

  2. Alejandro ECUADOR Internet Explorer Windows
    29 marzo 2010 at 12:47 #

    se puede delimitar la consulta?? o sea poner que llegue solo hasta el segundo o tercer nivel x ejemplo??

    Responder
    • GeekZero Google Chrome Windows
      31 marzo 2010 at 21:01 #

      Para la sintaxis "with" no lo he hecho nunca, pero podrías probar con un cursor que maneje cuantos niveles esta entrando, o algún tipo de campo que determine niveles (si es que esto lo tienes implementado, pero no es muy práctico y común)

      Responder
    • GeekZero PARAGUAY Google Chrome Windows
      2 marzo 2011 at 21:16 #

      Ya ha pasado mucho tiempo a esto, pero la solución es más que sencilla. No hace falta que modifiques la consulta recursiva, tan solo al

      1
2

      -- Desplegar ResultSet
SELECT * FROM EmpCTE

      que se encuentra al final del with debes agregarle una clausula top, ej:

      1
2

      -- Desplegar ResultSet
SELECT top 2 * FROM EmpCTE
      Responder

Responder