Requête récursive top manager

Version imprimable

28/10/2014, 14h24
vinch999

Requête récursive top manager

Salut,
Si par exemple j'ai une liste d'employés avec un lien vers le manager direct qui est aussi encodé en tant qu'employé (donc un self join)
J'aimerais retrouver le top manager pour chaque employé, exemple:

id_emp,employe,mgr_id
1,KING,NULL
2,MARC,1
3,JEAN,2
4,JULIEN,NULL
5,XAVIER,4
6,GEORGES,3
==>
id_emp,employe,mgr_id,top_mgr_id
1,KING,NULL,NULL
2,MARC,1,1
3,JEAN,2,1
4,JULIEN,NULL,NULL
5,XAVIER,4,4
6,GEORGES,1

Comment faire le plus efficacement pour toutes les lignes?

J'ai trouvé une solution (pas clean) lorsque je défini un employé en particulier mais je n'arrive pas à trouver comment faire pour la liste globale...:(

A l'avenir respectez la charte de postage en mettant DDL de vos tables et jeu d'essais sous forme INSERT !

DDL de la table :

Code:

1
2
3
4
CREATE TABLE T_EMPLOYE_EMP
(id_emp       INT PRIMARY KEY,
 employe      VARCHAR(32),
 mgr_id       INT FOREIGN KEY REFERENCES T_EMPLOYE_EMP (id_emp));

Jeu d'essai :

Code:

1
2
3
4
5
6
7
INSERT INTO T_EMPLOYE_EMP VALUES
(1,'KING',NULL),
(2,'MARC',1),
(3,'JEAN',2),
(4,'JULIEN',NULL),
(5,'XAVIER',4),
(6,'GEORGES',3);

Requête finale :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH 
T AS
(
SELECT id_emp, employe, mgr_id, mgr_id AS top_mgr_id, 0 AS niveau
FROM   T_EMPLOYE_EMP
UNION ALL
SELECT T.id_emp, T.employe, T.mgr_id, E.mgr_id, niveau + 1
FROM   T
       INNER JOIN T_EMPLOYE_EMP AS E
             ON T.top_mgr_id = E.id_emp),
TM AS             
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY id_emp ORDER BY niveau DESC) AS N
FROM   T
)
SELECT id_emp, employe, mgr_id, top_mgr_id
FROM   TM             
WHERE  N = 1;

Résultats :

Code:

1
2
3
4
5
6
7
8
id_emp      employe      mgr_id      top_mgr_id
----------- ------------ ----------- -----------
1           KING         NULL        NULL
2           MARC         1           NULL
3           JEAN         2           NULL
4           JULIEN       NULL        NULL
5           XAVIER       4           NULL
6           GEORGES      3           NULL

A +

Bonjour,

Vous pouvez essayer comme ceci :

Code:

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
 
 
Rec AS (
	SELECT	
			e.id_emp
		,	e.employe
		,	e.mgr_id
		,	COALESCE(p.mgr_id, p.id_emp)	AS top_mgr_id
	FROM	LaTable e
	LEFT JOIN LaTable p
		ON		p.id_emp = e.mgr_id
 
	UNION ALL
	SELECT 
			e.id_emp
		,	e.employe
		,	e.mgr_id
		,	p.mgr_id	AS top_mgr_id
	FROM	Rec e
	INNER JOIN LaTable p
		ON		p.id_emp = e.top_mgr_id
	WHERE p.mgr_id IS NOT NULL
)
	SELECT 
			e.id_emp
		,	e.employe
		,	e.mgr_id
		,	e.top_mgr_id
	FROM rec e
	LEFT JOIN LaTable p
		ON		p.id_emp = e.top_mgr_id
	WHERE p.mgr_id IS  NULL
;