BlackTDN Search

quinta-feira, 18 de junho de 2015

BlackTDN :: MSSQL Usando Consulta CTE Recursiva para montagem de Tree

Aproveitando a deixa de JLouW (Limites do AdvPL – parte 02 | Tudo em ADVPl) e de uma necessidade de otimizar uma estrutura de Produtos Customizada: segue uma dica de como montar uma “Estrutura Hierarquica (Tree)” usando uma Consulta CTE Recursiva no MSSQL.

(Obs.: Exemplo elaborado na versão 2014 do MSSQL)

/*
Exemplo de Consulta Hierarquica (Tree) usando MSSQL
Autor: Marinaldo de Jesus (http://www.blacktdn.com.br)
Adaptado dos Exemplos obtidos a partir das referências abaixo
Referencias:
--Ref.:https://technet.microsoft.com/pt-br/library/ms186243(v=sql.105).aspx
--Ref.:https://msdn.microsoft.com/pt-br/library/ms186755.aspx?f=3000&MSPPError=-2147217396
--Ref.:http://www.codeproject.com/Articles/328858/Implementing-multi-level-trees-in-MS-SQL-Server
--Ref.:https://msdn.microsoft.com/pt-br/library/bb677173.aspx?f=255&MSPPError=-2147217396
--Ref.:https://msdn.microsoft.com/en-us/library/ms190328.aspx
--Ref.:https://msdn.microsoft.com/en-us/library/ms190324.aspx

*/
--------------------------------------------------------------------
IF OBJECT_ID('[dbo].[MyEmployees]','U') IS NOT NULL
DROP TABLE [dbo].[MyEmployees];
GO
-- Create an Employee table.
CREATE TABLE [dbo].[MyEmployees]
(
EmployeeID int NOT NULL
,FirstName nvarchar(30) NOT NULL
,LastName nvarchar(40) NOT NULL
,Title nvarchar(50) NOT NULL
,DeptID smallint NOT NULL
,ManagerID int NULL
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO [dbo].[MyEmployees] VALUES
(1,N'Ken',N'Sánchez',N'Chief Executive Officer',16,NULL)
,(273,N'Brian',N'Welcker',N'Vice President of Sales',3,1)
,(274,N'Stephen',N'Jiang',N'North American Sales Manager',3,273)
,(275,N'Michael',N'Blythe',N'Sales Representative',3,274)
,(276,N'Linda',N'Mitchell',N'Sales Representative',3,274)
,(285,N'Syed',N'Abbas',N'Pacific Sales Manager',3,273)
,(286,N'Lynn',N'Tsoflias',N'Sales Representative',3,285)
,(16, N'David',N'Bradley',N'Marketing Manager',4,273)
,(23, N'Mary',N'Gibson',N'Marketing Specialist',4,16)
,(24, N'Mary 24',N'Gibson 24',N'Marketing Assistant SR',4,23)
,(25, N'Mary 25',N'Gibson 25',N'Marketing Specialist',4,16)
,(26, N'Mary 26',N'Gibson 26',N'Marketing Assistant SR',4,23)
,(27, N'Mary 27',N'Gibson 27',N'Marketing Assistant PL',4,24)
,(28, N'Mary 28',N'Gibson 28',N'Marketing Assistant JR',4,27)
,(29, N'Mary 29',N'Gibson 29',N'Marketing Assistant Trainee SR',4,28)
,(30, N'Mary 30',N'Gibson 30',N'Marketing Assistant SR',4,23)
,(31, N'Mary 31',N'Gibson 31',N'Marketing Assistant PL',4,24)
,(32, N'Mary 32',N'Gibson 32',N'Marketing Assistant JR',4,27)
,(33, N'Mary 33',N'Gibson 33',N'Marketing Assistant Trainee SR',4,28)
,(34, N'Mary 34',N'Gibson 34',N'Marketing Specialist',4,16)
,(35, N'Mary 35',N'Gibson 35',N'Marketing Specialist SR',4,34)
,(36, N'Mary 36',N'Gibson 36',N'Marketing Specialist PL',4,35)
,(37, N'Mary 37',N'Gibson 37',N'Marketing Specialist JR',4,36)
,(38, N'Mary 38',N'Gibson 38',N'Marketing Specialist Trainee SR',4,37)
,(39, N'Mary 39',N'Gibson 39',N'Marketing Specialist Trainee PL',4,38);
GO
--------------------------------------------------------------------
IF OBJECT_ID (N'[dbo].[ufn_FindReports]',N'TF') IS NOT NULL
DROP FUNCTION [dbo].[ufn_FindReports];
GO
CREATE FUNCTION [dbo].[ufn_FindReports]()
RETURNS @retFindReports TABLE
(
ID int IDENTITY(1,1) PRIMARY KEY
,MasterID int NOT NULL
,SuperID int NOT NULL
,ManagerID int NOT NULL
,Level int NOT NULL
,EmployeeID int NOT NULL
,NodeID nvarchar(3000) NOT NULL
)
AS
BEGIN
WITH DirectReports(
MasterID
,SuperID
,ManagerID
,Level
,EmployeeID
,NodeID
)
AS
(

(
SELECT m.EmployeeID AS MasterID
,0 AS SuperID
,IsNull(m.ManagerID,0)
,0 AS Level
,m.EmployeeID
,convert(varChar(3000),+cast(m.EmployeeID AS varchar)) AS NodeID
FROM dbo.MyEmployees AS m
WHERE m.ManagerID IS NULL
)
UNION ALL
(
SELECT d.MasterID
,(
CASE d.ManagerID
WHEN 0
THEN d.EmployeeID
ELSE d.ManagerID
END
) AS SuperID
,e.ManagerID
,d.Level+1
,e.EmployeeID
,convert(varChar(3000),d.NodeID+'.'+cast(e.EmployeeID AS varchar)) AS NodeID
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON d.EmployeeID=e.ManagerID
)
)
INSERT @retFindReports
SELECT *
FROM DirectReports
RETURN
END;
GO
SELECT REPLICATE(' ',4*(t.Level))+'+-->'+LTrim(Str(t.ManagerID,3)) AS ManagerID
,e.EmployeeID
,e.FirstName
,e.LastName
,REPLICATE(' ',4*(t.Level))+'+--> '+e.Title AS Title
,e.DeptID
,t.ID
,t.MasterID
,t.SuperID
,t.Level
,t.NodeID
FROM dbo.ufn_FindReports() t
LEFT JOIN dbo.MyEmployees e
ON e.EmployeeID=t.EmployeeID
ORDER BY t.MasterID
,t.NodeID
GO
--------------------------------------------------------------------

Tree - Usando CTE Recursiva


O código do exemplo poderá ser obtido aqui.


P.S.: No Oracle parece ser bem mais simples (Wiki: Hierarchical and recursive queries in SQL):


SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

 



[]s

иαldσ dj

Nenhum comentário:

Postar um comentário