Obtenir une hiérarchie

**fsmrel** · 18/12/2012, 18h43

Bonsoir,

Donc T108(Freddy) ne doit pas apparaître dans le résultat, bien que dépendant directement de T107(Raoul) : on ne s’intéresse qu’à l’ascendance, c'est bien cela ?

T106(Henri) ne doit pas apparaître ? Je suppose que c'est un oubli de votre part, sinon je ne vois pas pourquoi T105(Pascal) serait retenu. Qu'en est-il ?

**miniil** · 19/12/2012, 08h23

Bonjour,
Oui effectivement T106 doit apparaître mais pas T108.
C'était bien un oubli de ma part.
On ne s'occupe bien que de l’ascendance sur une personne donnée.
Y a-t-il moyen de faire cela?

**fsmrel** · 19/12/2012, 18h03

Bonsoir miniil,

Si le résultat est à fournir en une seule requête, il va falloir que les cracks en SQL se penchent sur votre problème, à savoir les SQLpro, Mikedavem, iberserk, elsuket, Waldar, pour ne citer qu’eux...

Rappel de la structure mettant Raoul en évidence (j'ai ajouté T007 et T9012) :

Pour ma part, du fait de la présence de T105 qu’on ne peut pas directement faire figurer a priori comme appartenant au chemin menant de T001 à T107, je pense qu’il faudra mémoriser ce chemin et ensuite l’exploiter.

Vous me direz qu’on peut produire ce chemin en partant de Raoul et en remontant de T107 à T001, mais cela pose d’autres problèmes.

Ce que je propose de mon côté n’est pas d’une élégance folle, mais bon.

Les tables que j’utilise ici :

Code SQL :

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
22
23
CREATE TABLE PERSONNES
(
        Id_P                CHAR(8)           NOT NULL
      , Nom_P               VARCHAR(96)       NOT NULL
    , CONSTRAINT PERSONNES_PK PRIMARY KEY (Id_P)   
) ;
CREATE TABLE TACHES
(
        Id_T              CHAR(8)           NOT NULL
      , Nom_T             VARCHAR(96)       NOT NULL
      , Parent_Id_T       CHAR(8)       
    , CONSTRAINT TACHE_PK PRIMARY KEY (Id_T)   
    , CONSTRAINT COMPOSITION_TACHE_FK2 FOREIGN KEY (Parent_Id_T) REFERENCES TACHES
    , CONSTRAINT COMPOSITION_CHK1 CHECK (Id_T <> Parent_Id_T)
) ;
CREATE TABLE PERSONNES_TACHES
(
        Id_P              CHAR(8)           NOT NULL
      , Id_T              CHAR(8)           NOT NULL
    , CONSTRAINT PERSONNES_TACHES_PK PRIMARY KEY (Id_P, Id_T)   
    , CONSTRAINT PERSONNES_TACHES_PERSONNES_FK FOREIGN KEY (Id_P) REFERENCES PERSONNES
    , CONSTRAINT PERSONNES_TACHES_TACHE_FK FOREIGN KEY (Id_T) REFERENCES TACHES
) ;

Un jeu d’essai :

Code SQL :

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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Albert', 'Albert, Le roi des gangsters') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Adolphe', 'Adolphe Amédée de la Foy') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Antoine', 'Antoine') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Bastien', 'Bastien, Un neveu de Berthe') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Caro', 'Carole, la reine de la java') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Denis', 'Denis dit "Le rapide"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Fernand', 'Fernand') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Freddy', 'Freddy') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gégé', 'Gérard, le bosseur') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gigi', 'Gigi, le chevelu') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gillou', 'Gilles, le pote à Dominique') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jojo', 'Georges, dit "Jojo La science"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Henri', 'Henri de Paris') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jacquot', 'Jacques, dit "Tête de veau"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jean', 'Jean, le majordome dévoué') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jérôme', 'Jérôme le chimiste') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Léna', 'Hélène de Troie en Champagne') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Loulou', 'Louis le sérieux') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Mado', 'Madame Mado, elle même') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Manu', 'Emmanuel le rassurant') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Marco', 'Marc la grosse voix') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Michou', 'Michel le géant') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Milou', 'Maria de Bahia') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Mimile', 'Emile à la méthode') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Nanard', 'Bernard, Le roi du système') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Nini', 'Nicole qui rigole') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Pascal', 'Pascal') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Patricia', 'Patricia, une nièce obéissante') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Paul', 'Paul') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Philou', 'Philippe, Le roi de la caméra') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Raoul', 'Raoul, candidat à la succession') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Riri', 'Richard le méthodique') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Roro', 'Roger la barraque') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Théo', 'Théo le faux derche') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Tomate', 'Tomate du clapier') ;
 
SELECT '' AS PERSONNES, * FROM PERSONNES ;
 
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T001', 'Présidence de l’entreprise', NULL) ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T002', 'Secrétariat général', 'T001') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T003', 'Direction de la MOE ', 'T001') ; 
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T004', 'Direction de la recherche', 'T001') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T005', 'Recherche (sécurité)', 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T006', 'Recherche (logique trivaluée)', 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T007', 'Recherche (logique quadrivaluée)', 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T090', 'Direction de l’informatique', 'T001') ; 
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T100', 'Direction de la production informatique', 'T090') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T101', 'Administration de la production informatique', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T102', 'Administration du système', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T103', 'Administration du SGBD', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T104', 'Relations avec les fournisseurs informatique', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T105', 'Administration du réseau', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T106', 'Serveurs Web', 'T105') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T107', 'SQL Server', 'T106') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T108', 'Gillou', 'T107') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T110', 'Direction des études informatiques', 'T090') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T120', 'Direction du projet "Référentiel des personnes"', 'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T121', 'Direction du s/projet "Référentiel des personnes physiques"', 'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T122', 'Direction du s/projet "Référentiel des entreprises"', 'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T123', 'Direction du s/projet "Référentiel des organismes"', 'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T130', 'Direction du projet "Référentiel des contrats"', 'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T131', 'Développement "Référentiel des contrats"', 'T130') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T140', 'Direction du projet "Référentiel des cotisations"', 'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T141', 'Direction du s/projet "Appel des cotisations"', 'T140') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T142', 'Développement "Appel des cotisations"', 'T141') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T160', 'Direction du s/projet "Ventilation compta"', 'T140') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T161', 'Développement "Ventilation compta"', 'T160') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T180', 'Direction du projet "Catalogue Produits"', 'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T190', 'Direction du projet "Prospection"', 'T180') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T191', 'Direction du s/projet "Prospection individuelle"', 'T190') ;
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T192', 'Direction du s/projet "Prospection de masse"', 'T190') ;
 
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T9001', 'Présidence de l’autre entreprise', NULL) ;   
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T9011', 's/dir de l’autre entreprise', 'T9001') ;   
INSERT INTO TACHES (Id_T, Nom_T, Parent_Id_T) VALUES ('T9012', 'sécrétariat de la s/dir de l’autre entreprise', 'T9011') ;   
 
SELECT '' AS TACHES, * FROM TACHES ;
 
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T001', 'Fernand') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T002', 'Bastien') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T003', 'Denis') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T004', 'Mado') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T005', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T005', 'Jean') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T006', 'Freddy') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T007', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T090', 'Paul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T100', 'Antoine') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T101', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T102', 'Patricia') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T103', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T104', 'Antoine') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T105', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T106', 'Henri') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T107', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T108', 'Freddy') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T110', 'Paul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T110', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T120', 'Bastien') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T121', 'Tomate') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T130', 'Nini') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T131', 'Léna') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T131', 'Roro') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T140', 'Marco') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T141', 'Loulou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T142', 'Mimile') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T142', 'Milou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T160', 'Philou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T180', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T180', 'Mado') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T190', 'Adolphe') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T191', 'Théo') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T192', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9001', 'Riri') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9011', 'Roro') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9012', 'Raoul') ;
 
SELECT '' AS PERSONNES_TACHES, * FROM PERSONNES_TACHES ;

Afin d’y voir plus clair, je commence par définir une vue pour la jointure récursive :

Code SQL :

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
22
23
24
25
26
27
CREATE VIEW ARBRE 
AS 
WITH W (Parent_Id_T, Id_T, Niveau, CheminTache, CheminPsn) AS
(
    SELECT x.Parent_Id_T, x.Id_T, 0 
         , CAST(x.Id_T AS varchar(MAX))
         , CAST(Nom_P as varchar(MAX)) 
    FROM   TACHES AS x JOIN PERSONNES_TACHES AS y ON x.Id_T = y.Id_T 
                       JOIN PERSONNES AS z ON y.Id_P = z.Id_P
    WHERE  x.Parent_Id_T IS NULL 
   UNION ALL
    SELECT x.Parent_Id_T, x.Id_T, Niveau + 1
         , CAST(CheminTache  + ';   ' +  x.Id_T as varchar(MAX))
         , CAST(CheminPsn + '/' + Nom_P as varchar(MAX))   
    FROM   W JOIN TACHES AS x ON W.Id_T = x.Parent_Id_T
             JOIN PERSONNES_TACHES  AS y ON x.Id_T = y.Id_T
             JOIN PERSONNES AS z ON y.Id_P = z.Id_P
)
SELECT COALESCE(W.Parent_Id_T, '') AS Parent_Id_T, W.Id_T
     , COALESCE(u.Nom_T, '') AS TacheParente, t.Nom_T AS TacheEnfant, COALESCE(z.Nom_P, '') AS Nom_P 
     , Niveau
     , CheminTache, CheminPsn
FROM   W LEFT JOIN PERSONNES_TACHES AS y ON W.Id_T = y.Id_T
         JOIN PERSONNES AS z ON y.Id_P = z.Id_P
         LEFT JOIN TACHES AS t ON W.Id_T = t.Id_T
         LEFT JOIN TACHES AS u ON W.Parent_Id_T = u.Id_T
;

Une partie du résultat d’un SELECT portant sur la vue :

Code :

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

1
2
3
4
5
6
7
8
Parent_Id_T    Id_T    Nom_P    Niveau    CheminTache
 
T004           T005    Raoul    2         T001/T004/T005 
T004           T007    Raoul    2         T001/T004/T007 
T100           T101    Raoul    3         T001/T090/T100/T101 
T106           T107    Raoul    5         T001/T090/T100/T105/T106/T107 
T190           T192    Raoul    5         T001;T090;T110;T180;T190;T192 
T9011          T9012   Raoul    2         T9001/T9011/T9012

Pour obtenir cette réduction, qui est en fait l’image de la représentation graphique pour la partie Raoul, il faut une requête éliminant les lignes non concernées. J’insère le résultat dans une table temporaire (@T) :

Code SQL :

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
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
DECLARE @Nom_P AS VARCHAR(64);
SET @Nom_P = 'Raoul, candidat à la succession' ;
 
DECLARE @T AS TABLE
(
        Parent_Id_T           char(8)           not null
      , Id_T                  char(8)           not null
      , Nom_P                 VARCHAR(96)       NOT NULL        
      , Niveau                varchar(8)        NOT NULL
      , CheminTache           varchar(1000)     not null
) 
;
WITH T1 AS 
(
 SELECT Parent_Id_T AS T1_Parent_Id_T, Id_T  AS T1_Id_T, Nom_P AS T1_Nom_P
      , Niveau AS T1_Niveau
      , CheminTache AS T1_CheminTache
      , LEN(CheminTache) AS LenCheminTache
 FROM   ARBRE
 WHERE  CheminPsn LIKE '%'+ @Nom_P + '%'
   AND  Nom_P = @Nom_P -- pour dégager les personnes affectées à la même tâche que Raoul, ainsi que les descendants 
)
 , T2 AS
(
 SELECT T1_Parent_Id_T AS T2_Parent_Id_T
      , T1_Id_T AS T2_Id_T
      , T1_Nom_P AS T2_Nom_P
      , T1_Niveau AS T2_Niveau
      , T1_CheminTache AS T2_CheminTache
 FROM   T1 JOIN arbre as x ON T1_CheminTache = LEFT(x.CheminTache, LEN(T1_CheminTache)) 
   AND  T1_CheminTache <> x.CheminTache  
 GROUP BY T1_Parent_Id_T, T1_Id_T, T1_Nom_P, T1_Niveau, T1_CheminTache
 HAVING COUNT(*) > 1
)
 , T3 AS
(
 SELECT T1_Parent_Id_T AS Parent_Id_T, T1_Id_T AS Id_T
      , T1_Nom_P AS Nom_P, T1_Niveau AS Niveau, T1_CheminTache AS CheminTache
FROM 
  (
   SELECT T1_Parent_Id_T, T1_Id_T, T1_Nom_P, T1_Niveau, T1_CheminTache 
   FROM   T1 
  EXCEPT 
   SELECT T2_Parent_Id_T, T2_Id_T, T2_Nom_P, T2_Niveau, T2_CheminTache 
   FROM T2
  ) as truc
)
INSERT INTO @T
    SELECT  Parent_Id_T, Id_T, Nom_P, Niveau, CheminTache  
    FROM    T3

;
Ce code est simplifiable, mais je n’ai pas cherché à faire dans la dentelle. Les chemins (colonne CheminTache) sont complets, mais il faut les remettre sous forme tabulaire :

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
22
23
Sequence    Parent_Id_T    Id_T    Niveau
 
1                          T001    1
2           T001           T004    2
3           T004           T005    3
4           T001           T004    2
5           T004           T007    3
6           T001           T090    2
7           T090           T100    3
8           T100           T101    4
9           T001           T090    2
10          T090           T100    3
11          T100           T105    4
12          T105           T106    5
13          T106           T107    6
14          T001           T090    2
15          T090           T110    3
16          T110           T180    4
17          T180           T190    5
18          T190           T192    6
19                         T9001   1
20          T9001          T9011   2
21          T9011          T9012   3

S’il faut en passer par là, espérons que les cracks de SQL sauront faire cela simplement à l’aide d’opérateurs et fonctions sioux. Pour ma part, je ne sais guère qu’en passer par un curseur (honte à moi ! Il y a des jours où l'on manque d'inspiration...)

Dans ce dernier résultat, reste à supprimer les doublons et mettre en forme (indentation et récupération de Raoul pour les tâches concernées).

A suivre...

**fsmrel** · 20/12/2012, 04h13

Bonjour miniil,

Suite...

En attendant les solutions élégantes, je vous transmets la méthode bourrin (curseur...

) pour remettre les lignes en colonnes si cela peut vous dépanner...

En entrée, on a la table temporaire @T dont j’ai parlé hier. En sortie, on a une table temporaire @R dans laquelle les chemins sont remis en colonnes :

Code SQL :

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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
/* ------------------------------------------------------------------
    La méthode boeuf pour remettre les lignes en colonnes...
*/ ------------------------------------------------------------------
 
DECLARE @R AS TABLE
(
        Sequence              INT           not null
      , Parent_Id_T           CHAR(8)       not null           
      , Id_T                  CHAR(8)       not null   
      , Nom_P                 VARCHAR(64)   not null DEFAULT ''      
      , Niveau                INT           not null
    , PRIMARY KEY (SEQUENCE)
    , UNIQUE (Parent_Id_T, Id_T) 
)
DECLARE @Indice AS INT, @Indice2 AS INT, @CheminLen AS INT ;
DECLARE @Parent AS CHAR(8), @Enfant AS CHAR(8), @Chemin AS VARCHAR(1000) ;
DECLARE @Niveau AS INT, @Sequence AS INT, @Count AS INT ;
 
DECLARE C1 CURSOR FOR
    SELECT CheminTache
    FROM   @T
    ORDER BY CheminTache ;
 
OPEN C1
FETCH C1 INTO @Chemin
 
SET @Sequence = 0
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CheminLen = LEN(LTRIM(Rtrim(@Chemin)))
 
        SET @Indice = 1
        SET @Indice2 = CHARINDEX('/', @Chemin, @Indice + 1)
        SET @Parent = ''
        SET @Niveau = 1
        SET @Enfant = SUBSTRING(@Chemin, @Indice, @Indice2 - @Indice)
        SET @Sequence = @Sequence + 1
 
        SET @Count = (SELECT COUNT(*) 
                      FROM   @R
                      WHERE  Parent_Id_T = @Parent
                        AND  Id_T = @Enfant)
 
        IF @Count = 0 -- Ne pas injecter de doublons {Parent_Id_T, Id_T}
            BEGIN
                INSERT INTO @R (Sequence, Parent_Id_T, Id_T, Niveau) VALUES (@Sequence, @Parent, @Enfant, @Niveau) ;
            END
        SET @Indice = 1 ; SET @Indice2 = 1
        SET @Parent = '' ; SET @Enfant = ''       
 
        WHILE @Indice < @CheminLen
           BEGIN
                SET @Indice2 = CHARINDEX('/', @Chemin, @Indice + 1)
                IF @Indice2 = 0
                    BEGIN
                        SET @Indice2 = @CheminLen + 1    
                    END
                    SET @Parent = SUBSTRING(@Chemin, @Indice, @Indice2 - @Indice)
                    SET @Indice = @Indice2 + 1
                    IF @Indice < @CheminLen
                        BEGIN
                            SET @Indice2 = CHARINDEX('/', @Chemin, @Indice + 1)
                            IF @Indice2 = 0
                                BEGIN
                                    SET @Indice2 = @CheminLen + 1  
                                END  
                            SET @Enfant = SUBSTRING(@Chemin, @Indice , @Indice2 - (@Indice))
                            SET @Sequence = @Sequence + 1
                            SET @Niveau = @Niveau + 1 
 
                            SET @Count = (SELECT COUNT(*) 
                                          FROM   @R
                                          WHERE  Parent_Id_T = @Parent
                                            AND  Id_T = @Enfant)
 
                            IF @Count = 0 -- Ne pas injecter de doublons {Parent_Id_T, Id_T}
                                BEGIN
                                    INSERT INTO @R (Sequence, Parent_Id_T, Id_T, Niveau)  VALUES (@Sequence, @Parent, @Enfant, @Niveau) ;
                               END
                        END
            END
            FETCH C1 INTO @Chemin
    END
CLOSE C1
DEALLOCATE C1 ;
 
--------------------------------------------------------------------
 
/* -------------------
    Pour voir
*/ -------------------
 
SELECT '' AS '@R', * from @R ORDER BY SEQUENCE

On met ensuite à jour la table @R pour y incorporer le nom de Raoul :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
/* ---------------------------------
    Récupération du nom de Raoul
*/ ---------------------------------
 
UPDATE @R
    SET Nom_P = @Nom_P
    WHERE Id_T IN (SELECT DISTINCT x.Id_T
                   FROM   @R AS x JOIN PERSONNES_TACHES AS y ON x.Id_T = y.Id_T
                                  JOIN PERSONNES AS z ON y.Id_P = z.Id_P
                   WHERE  z.Nom_P = @Nom_P) ;

On peut alors récupérer le résultat demandé :

Code SQL :

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
/* ------------------------
    Au résultat !
*/ ------------------------
 
DECLARE @S AS TABLE
(
        Résultat     VARCHAR(1000)
) ;
 
INSERT INTO @S 
        SELECT REPLICATE('- ', 2 * (Niveau - 1)) + ' ' + RTRIM(Id_T)  + ' ; ' + Nom_P
        FROM @R 
        ORDER BY SEQUENCE
;
 
SELECT Résultat FROM @S ;

=>

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
T001 ; 
- -  T004 ; 
- - - -  T005 ; Raoul, candidat à la succession
- - - -  T007 ; Raoul, candidat à la succession
- -  T090 ; 
- - - -  T100 ; 
- - - - - -  T101 ; Raoul, candidat à la succession
- - - - - -  T105 ; 
- - - - - - - -  T106 ; 
- - - - - - - - - -  T107 ; Raoul, candidat à la succession
- - - -  T110 ; 
- - - - - -  T180 ; Raoul, candidat à la succession
- - - - - - - -  T190 ; 
- - - - - - - - - -  T192 ; Raoul, candidat à la succession
 T9001 ; 
- -  T9011 ; 
- - - -  T9012 ; Raoul, candidat à la succession

Bon courage !

**SQLpro** · 20/12/2012, 14h47

Envoyé par fsmrel

Bonjour miniil,

Suite...

En attendant les solutions élégantes, je vous transmets la méthode bourrin (curseur...

Comment François... Tu n'a pas honte !!! À ton âge...

A +

**fsmrel** · 20/12/2012, 15h34

Ave Fred,

A tout péché miséricorde...

**Waldar** · 24/12/2012, 14h46

Comme c'est bientôt Noël et que j'avais laissé tomber miniil, je termine cette entreprise collégiale de résolution de son problème et arrive avec une solution sans curseur :

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
22
23
24
25
26
27
28
with Hierarchie (Id_T_Chemin, Id_T, Nom_T, lvl) AS
(
SELECT cast(Id_T as varchar(max)), Id_T, Nom_T, 1
  FROM TACHES
 WHERE Parent_Id_T IS NULL
 union ALL
SELECT H.Id_T_Chemin + '/' + T.Id_T
     , T.Id_T, T.Nom_T, H.lvl + 1
  FROM Hierarchie AS H
  JOIN TACHES     AS T
    ON T.Parent_Id_T = H.Id_T
)
    select case when HI.lvl = 1 then HI.Id_T else replicate('-', 2 * (HI.lvl - 1)) + ' ' + HI.Id_T end
         + case when PE.Id_P is null then '' else ' - ' + PE.Id_P end as Representation
      from Hierarchie       AS HI
 left join PERSONNES_TACHES AS PT
inner join PERSONNES        AS PE
        ON PE.Id_P = PT.Id_P
       AND PE.Id_P = 'Raoul'
        ON PT.Id_T = HI.Id_T
     where exists (select null
                     from Hierarchie       as HI2
                     join PERSONNES_TACHES AS PT2 ON PT2.Id_T = HI2.Id_T
                     join PERSONNES        AS PE2 ON PE2.Id_P = PT2.Id_P
                    where PE2.Id_P = 'Raoul'
                      and CHARINDEX(HI.Id_T, HI2.Id_T_Chemin) > 0)
  order by HI.Id_T_Chemin asc
         , HI.lvl         asc;

Representation
-----------------------
T001
-- T004
---- T005 - Raoul
---- T007 - Raoul
-- T090
---- T100
------ T101 - Raoul
------ T105
-------- T106
---------- T107 - Raoul
---- T110
------ T180 - Raoul
-------- T190
---------- T192 - Raoul
T9001
-- T9011
---- T9012 - Raoul

**fsmrel** · 24/12/2012, 16h57

Miniil,

SuperWaldar vous a fait un très beau cadeau, sa solution est concise et élégante, clairement c’est lui le meilleur, on lui dit

, on le

et on l’

!

**miniil** · 31/01/2013, 14h46

J'ai été longtemps absente, sorry...
Merci déjà pour votre aide jusqu'ici.

Mais je reviens à nouveau vers vous pour cette requête car celle-ci vient à nouveau d'être compliquée.

Je reprends les scripts de création de la DB :

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
22
23
24
25
 
CREATE TABLE PERSONNES
(
        Id_P                CHAR(8)           NOT NULL
      , Nom_P               VARCHAR(96)       NOT NULL
    , CONSTRAINT PERSONNES_PK PRIMARY KEY (Id_P)   
) ;
CREATE TABLE TACHES
(
        Id_T              CHAR(8)           NOT NULL
      , Nom_T             VARCHAR(96)       NOT NULL
			, Resp							VARCHAR(96)
      , Parent_Id_T       CHAR(8)       
    , CONSTRAINT TACHE_PK PRIMARY KEY (Id_T)   
    , CONSTRAINT COMPOSITION_TACHE_FK2 FOREIGN KEY (Parent_Id_T) REFERENCES TACHES
    , CONSTRAINT COMPOSITION_CHK1 CHECK (Id_T <> Parent_Id_T)
) ;
CREATE TABLE PERSONNES_TACHES
(
        Id_P              CHAR(8)           NOT NULL
      , Id_T              CHAR(8)           NOT NULL
    , CONSTRAINT PERSONNES_TACHES_PK PRIMARY KEY (Id_P, Id_T)   
    , CONSTRAINT PERSONNES_TACHES_PERSONNES_FK FOREIGN KEY (Id_P) REFERENCES PERSONNES
    , CONSTRAINT PERSONNES_TACHES_TACHE_FK FOREIGN KEY (Id_T) REFERENCES TACHES
) ;

=> Un champs a été ajouté dans la table TACHES : Resp (Responsable de la tâche).

Le jeu de données modifié en conséquence :

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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Albert', 'Albert, Le roi des gangsters') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Adolphe', 'Adolphe Amédée de la Foy') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Antoine', 'Antoine') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Bastien', 'Bastien, Un neveu de Berthe') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Caro', 'Carole, la reine de la java') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Denis', 'Denis dit "Le rapide"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Fernand', 'Fernand') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Freddy', 'Freddy') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gégé', 'Gérard, le bosseur') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gigi', 'Gigi, le chevelu') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Gillou', 'Gilles, le pote à Dominique') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jojo', 'Georges, dit "Jojo La science"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Henri', 'Henri de Paris') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jacquot', 'Jacques, dit "Tête de veau"') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jean', 'Jean, le majordome dévoué') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Jérôme', 'Jérôme le chimiste') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Léna', 'Hélène de Troie en Champagne') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Loulou', 'Louis le sérieux') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Mado', 'Madame Mado, elle même') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Manu', 'Emmanuel le rassurant') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Marco', 'Marc la grosse voix') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Michou', 'Michel le géant') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Milou', 'Maria de Bahia') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Mimile', 'Emile à la méthode') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Nanard', 'Bernard, Le roi du système') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Nini', 'Nicole qui rigole') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Pascal', 'Pascal') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Patricia', 'Patricia, une nièce obéissante') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Paul', 'Paul') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Philou', 'Philippe, Le roi de la caméra') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Raoul', 'Raoul, candidat à la succession') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Riri', 'Richard le méthodique') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Roro', 'Roger la barraque') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Théo', 'Théo le faux derche') ;
INSERT INTO PERSONNES (Id_P, Nom_P) VALUES ('Tomate', 'Tomate du clapier') ;
 
SELECT '' AS PERSONNES, * FROM PERSONNES ;
 
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T001', 'Présidence de l’entreprise', NULL, NULL) ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T002', 'Secrétariat général', NULL, 'T001') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T003', 'Direction de la MOE ', NULL, 'T001') ; 
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T004', 'Direction de la recherche', NULL, 'T001') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T005', 'Recherche (sécurité)', NULL, 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T006', 'Recherche (logique trivaluée)', NULL, 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T007', 'Recherche (logique quadrivaluée)', NULL, 'T004') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T090', 'Direction de l’informatique', NULL, 'T001') ; 
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T100', 'Direction de la production informatique', 'Raoul', 'T090') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T101', 'Administration de la production informatique', NULL,'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T102', 'Administration du système', NULL, 'T100') ; 
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T103', 'Administration du SGBD', NULL, 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T104', 'Relations avec les fournisseurs informatique', 'Raoul', 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T105', 'Administration du réseau', NULL, 'T100') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T106', 'Serveurs Web', NULL, 'T105') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T107', 'SQL Server', NULL, 'T106') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T108', 'Gillou', NULL, 'T107') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T110', 'Direction des études informatiques', NULL, 'T090') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T120', 'Direction du projet "Référentiel des personnes"', NULL, 'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T121', 'Direction du s/projet "Référentiel des personnes physiques"', NULL, 'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T122', 'Direction du s/projet "Référentiel des entreprises"', NULL, 'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T123', 'Direction du s/projet "Référentiel des organismes"', NULL,'T120') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T130', 'Direction du projet "Référentiel des contrats"', NULL,'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T131', 'Développement "Référentiel des contrats"', NULL,'T130') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T140', 'Direction du projet "Référentiel des cotisations"', NULL,'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T141', 'Direction du s/projet "Appel des cotisations"', NULL, 'T140') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T142', 'Développement "Appel des cotisations"', NULL,'T141') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T160', 'Direction du s/projet "Ventilation compta"', 'Raoul', 'T140') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T161', 'Développement "Ventilation compta"', NULL, 'T160') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T180', 'Direction du projet "Catalogue Produits"', NULL,'T110') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T190', 'Direction du projet "Prospection"', NULL, 'T180') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T191', 'Direction du s/projet "Prospection individuelle"', 'Raoul', 'T190') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T192', 'Direction du s/projet "Prospection de masse"', NULL, 'T190') ;
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T9001', 'Présidence de l’autre entreprise', NULL, NULL) ;   
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T9011', 's/dir de l’autre entreprise', NULL, 'T9001') ;   
INSERT INTO TACHES (Id_T, Nom_T, Resp, Parent_Id_T) VALUES ('T9012', 'sécrétariat de la s/dir de l’autre entreprise', NULL, 'T9011') ;   
 
SELECT '' AS TACHES, * FROM TACHES ;
 
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T001', 'Fernand') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T002', 'Bastien') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T003', 'Denis') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T004', 'Mado') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T005', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T005', 'Jean') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T006', 'Freddy') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T007', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T090', 'Paul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T100', 'Antoine') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T101', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T102', 'Patricia') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T103', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T104', 'Antoine') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T105', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T106', 'Henri') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T107', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T108', 'Freddy') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T110', 'Paul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T110', 'Pascal') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T120', 'Bastien') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T121', 'Tomate') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T130', 'Nini') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T131', 'Léna') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T131', 'Roro') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T140', 'Marco') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T141', 'Loulou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T142', 'Mimile') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T142', 'Milou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T160', 'Philou') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T180', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T180', 'Mado') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T190', 'Adolphe') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T191', 'Théo') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T192', 'Raoul') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9001', 'Riri') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9011', 'Roro') ;
INSERT INTO PERSONNES_TACHES (Id_T, Id_P) VALUES ('T9012', 'Raoul') ;
 
SELECT '' AS PERSONNES_TACHES, * FROM PERSONNES_TACHES ;

Donc maintenant le but est d'obtenir le même résultat que le précédent mais avec en plus les tâches dont Raoul est responsable (+ les tâches parents).

Est-il possible d'obtenir ça en une seule requête?

D'avance merci pour votre aide

**aieeeuuuuu** · 01/02/2013, 12h59

Bonjour,

Est-ce que ceci vous donne ce que vous voulez ?

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
 
WITH Hierarchie (Id_T_Chemin, Id_T, Nom_T, lvl, resp) AS
(
SELECT cast(Id_T AS varchar(max)), Id_T, Nom_T, 1, resp
  FROM TACHES
 WHERE Parent_Id_T IS NULL
 union ALL
SELECT H.Id_T_Chemin + '/' + T.Id_T
     , T.Id_T, T.Nom_T, H.lvl + 1, T.resp
  FROM Hierarchie AS H
  JOIN TACHES     AS T
    ON T.Parent_Id_T = H.Id_T
)
    SELECT case when HI.lvl = 1 then HI.Id_T else replicate('-', 2 * (HI.lvl - 1)) + ' ' + HI.Id_T end
         + case when PE.Id_P IS NULL then '' else ' - ' + PE.Id_P end 
         + case when resp = 'Raoul' THEN ' - Raoul (responsable)' ELSE '' END  
         AS Representation
      FROM Hierarchie       AS HI
 LEFT JOIN PERSONNES_TACHES AS PT
INNER JOIN PERSONNES        AS PE
        ON PE.Id_P = PT.Id_P
       AND PE.Id_P = 'Raoul'
        ON PT.Id_T = HI.Id_T
     WHERE EXISTS (SELECT NULL
                     FROM Hierarchie       AS HI2
                     JOIN PERSONNES_TACHES AS PT2 ON PT2.Id_T = HI2.Id_T
                     JOIN PERSONNES        AS PE2 ON PE2.Id_P = PT2.Id_P
                    WHERE PE2.Id_P = 'Raoul'
                      AND CHARINDEX(HI.Id_T, HI2.Id_T_Chemin) > 0)
         OR EXISTS (SELECT NULL
                     FROM Hierarchie       AS HI2
                    WHERE HI2.resp = 'Raoul'
                      AND CHARINDEX(HI.Id_T, HI2.Id_T_Chemin) > 0)
  ORDER BY HI.Id_T_Chemin ASC
         , HI.lvl         ASC;

Par contre, vous devriez plutot ajouter une colonne ID_P_Responsable dans votre table Tache, indiquant l'identifiant de la personne responsable, plutot que directement une colonne VARCHAR(96) censée contenir directement son nom...

Il faudra alors légèrement modifier la requête en conséquence, en ajouter des jointures avec la table personne...

**miniil** · 01/02/2013, 14h07

Bonjour,

Merci de votre réponse.

Malheureusement, je n'obtiens pas le résultat escompté lorsque je reporte le problème à une DB plus importante.

Si vous (ou quelqu'un d'autre) avez une autre solution, je suis preneuse.

**aieeeuuuuu** · 01/02/2013, 14h36

J'avais laissé trainer une erreur de copier coller dans la dernière sous requête, j'ai modifié directement dans mon post au dessus, pouvez vous réessayer, et si le résultat n'est toujours pas bon, pouvez indiquer ce qui ne va pas ?

**miniil** · 01/02/2013, 16h00

Cette fois-ci ça semble correcte, j'ai tout mes résultats, il ne me reste plus qu'à trouver le bon ORDER BY pour avoir les résultats dans l'ordre exact car sur un grand nombre de données, ça s'emmêle un peu

Milles mercis pour votre aide.

**aieeeuuuuu** · 01/02/2013, 16h03

comment voulez vous classer les résultats ?

**miniil** · 01/02/2013, 19h01

Merci j'ai trouvé en ajoutant un nouvel ORDER BY

Grace à vous, je crois que j'ai enfin les bons résultats. Ils seront passés à la loupe dès lundi pour plusieurs personnes et je confirmerai

Bon WE et encore merci pour votre aide.

**miniil** · 07/02/2013, 11h26

Et bien voilà, je viens confirmer que la requête donne bien les résultats voulus et donc vous remercier une dernière fois pour l'aide que vous m'avez apportée.

Obtenir une hiérarchie

MS SQL Server

Discussions similaires

Partager

Partager