[SQL] 1 Branche avec CONNECT BY

**alx13** · 19/04/2011, 19h51

Bonsoir,

Est-ce avec une requête connect by et start with.
On peut obtenir qu'une "branche" d'un arbre.

c'est à dire je voudrais voir apparaître que la partie rouge des données
ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  level |  employee     | empno | manager
------- +-------------     +-------+---------
     1   | KING              |  7839 |
     2   |    JONES         |  7566 |    7839
     3   |       SCOTT      |  7788 |    7566
     4   |          ADAMS  |  7876 |    7788
     3   |       FORD       |  7902 |    7566
     4   |          SMITH   |  7369 |    7902
     2   |    BLAKE         |  7698 |    7839
     3   |       ALLEN      |  7499 |    7698
     3   |       WARD      |  7521 |    7698
     3   |       MARTIN    |  7654 |    7698
     3   |       TURNER   |  7844 |    7698
     3   |       JAMES      |  7900 |    7698
     2   |    CLARK         |  7782 |    7839
     3   |       MILLER     |  7934 |    7782

Or a moins de passer par un union
qui me sortira le level 1 puis l'autre partie de la branche "blake"

je ne vois pas comment....

Si il y a une solution sans union je suis preneur

Merci

**Pomalaix** · 20/04/2011, 01h08

Pour le cas précis, vous pouvez utiliser ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
col nom format A30
select lpad('-', (level-1)*3, '-') || ename nom
from emp
start with ename='KING'
connect by prior empno=mgr
AND (prior ename='BLAKE' or ename='BLAKE');
 
NOM
------------------------------
KING
---BLAKE
------ALLEN
------WARD
------MARTIN
------TURNER
------JAMES

Mais ça ne marche que parce que BLAKE n'a pas de "petits enfants".

Dans un cas plus général, par exemple tous les descendants de JONES, il faut trouver un critère respecté par tous les descendants.
On peut jouer sur SYS_CONNECT_BY_PATH :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select lpad('-', level-1, '-') || ename ename, sys_connect_by_path(ename, '-') chaine
from emp
start with mgr is null
connect by prior empno=mgr;
 
ENAME                          CHAINE
------------------------------ ----------------------------------------
KING                           -KING
-JONES                         -KING-JONES
--SCOTT                        -KING-JONES-SCOTT
---ADAMS                       -KING-JONES-SCOTT-ADAMS
--FORD                         -KING-JONES-FORD
---SMITH                       -KING-JONES-FORD-SMITH
-BLAKE                         -KING-BLAKE
--ALLEN                        -KING-BLAKE-ALLEN
--WARD                         -KING-BLAKE-WARD
--MARTIN                       -KING-BLAKE-MARTIN
--TURNER                       -KING-BLAKE-TURNER
--JAMES                        -KING-BLAKE-JAMES
-CLARK                         -KING-CLARK
--MILLER                       -KING-CLARK-MILLER

Malheureusement cette fonction n'est pas utilisable dans un WHERE, donc il faut passer par une sous-requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
col ename format A30
col chaine format A40
select ename, empno, mgr from
   (select lpad('-', (level-1)*3, '-') || ename ename, empno, mgr, sys_connect_by_path(ename, '-') chaine
   from emp
   start with mgr is null
   connect by prior empno=mgr)
where chaine='-KING'
   or chaine like '-KING-JONES%';
 
ENAME                               EMPNO        MGR
------------------------------ ---------- ----------
KING                                 7839
---JONES                             7566       7839
------SCOTT                          7788       7566
---------ADAMS                       7876       7788
------FORD                           7902       7566
---------SMITH                       7369       7902

Si vous ne voulez garder qu'une seule branche, cette solution n'est évidemment pas plus légère que l'union, mais si vous devez garder plusieurs branches, il suffira d'ajouter les conditions OR correspondantes.

**alx13** · 20/04/2011, 08h36

Merci Pomalaix,

ça me plaît bien comme solution, je vais essayer de ce pas.

**alx13** · 20/04/2011, 13h32

ça marche bien

Merci encore

**laurentschneider** · 20/04/2011, 18h32

Envoyé par alx13

ça marche bien

Tu m'en diras tant

Attendu que la hiérarchie est bâtie au fur et à mesure, au moment où l'on accède à un ancêtre de BLAKE, on ne peut pas encore savoir qu'il s'agisse d'un ancêtre de BLAKE. Il faut donc une sous-requête, je serais curieux de voir une soluce avec seulement une union... et qui bien sûre prévoit les arrières-arrières-arrières-grand-pères et arrières-arrières-arrières-petits-enfants

Je suis sûr qu'il y a mieux, mais voici un exemple avec un CTE récursif et un IN (subquery).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH e (l, empno, ename, mgr, child) AS (
SELECT 1,empno,ename,mgr,CASE WHEN ename = 'BLAKE' THEN 1 END
FROM emp WHERE mgr IS NULL
UNION ALL
SELECT e.l + 1, f.empno, f.ename, f.mgr,
  CASE WHEN f.ename != 'BLAKE' THEN e.child ELSE 1 END
FROM emp f, e WHERE e.empno = f.mgr)
SEARCH DEPTH FIRST BY ename SET ord,
m (empno, mgr) AS (
SELECT empno, mgr FROM emp WHERE ename = 'BLAKE'
UNION ALL
SELECT f.empno, f.mgr FROM emp f, m WHERE m.mgr = f.empno)
SELECT l,lpad(' ',l*3-3)||ename,empno,mgr FROM e
WHERE child = 1 OR empno IN (SELECT mgr FROM m)
ORDER BY ord;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
     level employee              empo    manager
---------- --------------- ---------- ----------
         1 KING                  7839           
         2    BLAKE              7698       7839
         3       ALLEN           7499       7698
         3       JAMES           7900       7698
         3       MARTIN          7654       7698
         3       TURNER          7844       7698
         3       WARD            7521       7698

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH e (l, empno, ename, mgr, child) AS (
SELECT 1,empno,ename,mgr,CASE WHEN ename =  'ADAMS'THEN 1 END
FROM emp WHERE mgr IS NULL
UNION ALL
SELECT e.l + 1, f.empno, f.ename, f.mgr,
  CASE WHEN f.ename !=  'ADAMS'THEN e.child ELSE 1 END
FROM emp f, e WHERE e.empno = f.mgr)
SEARCH DEPTH FIRST BY ename SET ord,
m (empno, mgr) AS (
SELECT empno, mgr FROM emp WHERE ename =  'ADAMS'
UNION ALL
SELECT f.empno, f.mgr FROM emp f, m WHERE m.mgr = f.empno)
SELECT l,lpad(' ',l*3-3)||ename,empno,mgr FROM e
WHERE child = 1 OR empno IN (SELECT mgr FROM m)
ORDER BY ord;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
     level employee              empo    manager
---------- --------------- ---------- ----------
         1 KING                  7839           
         2    JONES              7566       7839
         3       SCOTT           7788       7566
         4          ADAMS        7876       7788

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH e (l, empno, ename, mgr, child) AS (
SELECT 1,empno,ename,mgr,CASE WHEN ename = 'KING' THEN 1 END
FROM emp WHERE mgr IS NULL
UNION ALL
SELECT e.l + 1, f.empno, f.ename, f.mgr,
  CASE WHEN f.ename != 'KING' THEN e.child ELSE 1 END
FROM emp f, e WHERE e.empno = f.mgr)
SEARCH DEPTH FIRST BY ename SET ord,
m (empno, mgr) AS (
SELECT empno, mgr FROM emp WHERE ename = 'KING'
UNION ALL
SELECT f.empno, f.mgr FROM emp f, m WHERE m.mgr = f.empno)
SELECT l,lpad(' ',l*3-3)||ename,empno,mgr FROM e
WHERE child = 1 OR empno IN (SELECT mgr FROM m)
ORDER BY ord;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
     level employee              empo    manager
---------- --------------- ---------- ----------
         1 KING                  7839           
         2    BLAKE              7698       7839
         3       ALLEN           7499       7698
         3       JAMES           7900       7698
         3       MARTIN          7654       7698
         3       TURNER          7844       7698
         3       WARD            7521       7698
         2    CLARK              7782       7839
         3       MILLER          7934       7782
         2    JONES              7566       7839
         3       FORD            7902       7566
         4          SMITH        7369       7902
         3       SCOTT           7788       7566
         4          ADAMS        7876       7788

**laurentschneider** · 20/04/2011, 18h38

Pour les vieilles versions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH e AS (
SELECT level l, empno,ename,mgr,sys_connect_by_path(ename,'/')||'/' p
FROM emp
start with mgr is null
connect by prior empno=mgr),
m AS (
SELECT mgr
FROM emp
start with ename='BLAKE'
connect by empno=prior mgr)
SELECT l "level",lpad(' ',l*3-3)||ename "employee",empno "empo",mgr "manager"
FROM e
WHERE p like '%/BLAKE/%' OR empno IN (SELECT mgr FROM m);

**Pomalaix** · 20/04/2011, 23h09

Envoyé par laurentschneider

Tu m'en diras tant

Il faudrait déjà s'assurer qu'on répond à la même question !

De la description initiale, je traduis : affiche-moi KING, BLAKE et ses subordonnés. (Et je suppose que BLAKE est relié sans intermédiaire à KING, mais l'adaptation est mineure pour se placer dans un cas plus général).

Vous vous traduisez visiblement par : affiche-moi toute la chaîne hiérarchique dans laquelle se situe BLAKE, autrement dit ses supérieurs depuis le grand chef, BLAKE lui-même et ses subordonnés.

**laurentschneider** · 21/04/2011, 10h14

On peut obtenir qu'une "branche" d'un arbre.

Il s'agit bien d'une question hiérarchique.

Bon, à la question : comment obtenir ce qu'il y a en rouge on aurait pu répondre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from table(sys.odcivarchar2list('KING','BLAKE','ALLEN','WARD','MARTIN','TURNER','JAMES'))

**Pomalaix** · 21/04/2011, 11h47

Envoyé par laurentschneider

Bon, à la question : comment obtenir ce qu'il y a en rouge on aurait pu répondre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from table(sys.odcivarchar2list('KING','BLAKE','ALLEN','WARD','MARTIN','TURNER','JAMES'))

Mouarf !

Il ne reste plus qu'à attendre que le demandeur repasse pour nous expliquer ce qu'il voulait réellement.

[SQL] 1 Branche avec CONNECT BY

SQL Oracle

Discussions similaires

Partager

Partager