Récupérer la chaine la plus longue

**Erakis** · 14/09/2009, 22h45

Bonjour à tous,

J'aimerais récupérer la valeur de actWBS qui à la chaine la plus longue mais aussi qui débute par "1.1." J'aimerais que la requête retourne le actCode et le actWBS.

Voici la table en question :

Code :

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

1
2
3
4
5
6
7
8
9
actCode, actWBS
  258	   1.	
  259	   2.	
  260	   3.	
  261	   1.1.	
  262	   1.2.	
  263	   1.3.	
  264	   1.1.1.	
  265	   1.1.1.1.

Voilà ce que j'ai trouvé comme requête, toutefois je ne crois pas que ce soit optimal.

Cela fait un bail que j'ai pas touché à SQL

En fait, cette requête servira à mettre à jour une autre table. La requête de mise à jour sera quant à elle assez complexe pour mes compétences rouillées. Cependant, pourquoi pas commencer par la première étape

Code :

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

1
2
3
4
5
SELECT TOP 1 actWBS, actCode, MAX(LEN(actWBS)) AS MaxWBSLen
FROM Activite
WHERE SUBSTRING(actWBS, 1, LEN('1.1.')) = '1.1.'
GROUP BY actWBS, actCode
ORDER BY MaxWBSLen DESC

Quelqu'un pourrait m'éclairer ?

Bonne journée

**mikedavem** · 14/09/2009, 23h04

Bonsoir,

Voici une réponse :

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
 
DECLARE @t TABLE
(
 actCode INT,
 actWBS VARCHAR(20)
);
 
INSERT INTO @t VALUES (258,'1.');
INSERT INTO @t VALUES (259,'2.');
INSERT INTO @t VALUES (260,'3.');
INSERT INTO @t VALUES (261,'1.1');
INSERT INTO @t VALUES (262,'1.2');
INSERT INTO @t VALUES (263,'1.3');
INSERT INTO @t VALUES (264,'1.1.1');
INSERT INTO @t VALUES (265,'1.1.1.1');
 
SELECT TOP 1
	*
FROM @t
WHERE actWBS LIKE '1.1.%'
ORDER BY LEN(actWBS) DESC;

++

**Erakis** · 14/09/2009, 23h40

Merci beaucoup, c'est plus optimal comme solution

Voilà où je veux en venir avec ma requête. J'ai une table qui contient des activités.

Voici un apperçu de la table [Activite] en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
actCode,   actWBS,    actProjetCode
  258	     1.	         45
  259	     2.	         45
  260	     3.	         45
  261	     1.1.	 45
  262	     1.2.	 45
  263	     1.3.	 45
  264	     1.1.1.      45
  265	     1.1.1.1.    45

Bref, cette table contient des activitées et des sous activitées. Et c'est à l'aide du WBS que l'on s'y retrouve.

Et voilà une autre table intermédiaire [Experimentation] qui elle, contient les expérimentations attribuées aux activitées:

Code :

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

1
2
3
4
 
expCode, expActCode
  1	   261
  2        265

En temps normal, une activitée ayant une sous activitée ne devrait pas posséder d'expérimentation. Donc l'expérimentation avec le code expCode (1) est INVALIDE.

Ce que je cherche à faire, c'est de trouver la dernière sous-activités d'une activité et d'y relocaliser l'expérimentation.

Oufff.... désolé. J'ai essayé d'être le plus claire possible

Voilà ce que j'ai réalisé jusqu'ici :

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
 
/*
   Création d'une table temporaire afin d'y stocker les expérimentations situé 
   dans une mauvaise activitée. Le dernier champ de cette table contiendra la
   clé de l'activité où devrait être déplacer l'expérimentation.
*/
DECLARE @ExpInvalide TABLE 
(
    expCode INT,
    actCode INT,
    actWBS NVARCHAR(40),
    actProjetCode INT,
    DeplacerDansSousActivite INT
)
 
/* 
   Insérer à la table temporaire les expérimentations situé 
   dans une mauvaise activitée et par conséquent y rechercher aussi la
   clé de l'activité où elle devrait être.
*/
INSERT INTO @ExpInvalide
SELECT TbExp.expCode
           TbActivite.actCode, TbActivite.actWBS, TbActivite.actProjetCode, 
           TbActivitieValide.actCode
FROM Activite AS TbActivite
        INNER JOIN Experimentation AS TbExp ON actCode = expActCode
        RIGHT JOIN 
        (
             -- Trouver la dernière sous-activité de l'activité mère (parcourue actuellement à traver la table temporaire)
	SELECT TOP 1 actCode
	FROM Activite AS TbActivitieValide
	WHERE actProjetCode = TbActivite.actProjetCode
	      AND actWBS LIKE (TbActivite.actWBS  + '%')
             ORDER BY LEN(actWBS) DESC
 
     ) AS TbActivitieValide ON TbActivitieValide.actCode = TbActivite.actCode
 
WHERE EXISTS
	 (
	SELECT actCode 
	FROM Activite AS TbNotExists 
	WHERE TbNotExists.actProjetCode = TbActivite.actProjetCode 
		AND SUBSTRING(TbNotExists.actWBS, 1, LEN(TbActivite.actWBS)) = TbActivite.actWBS
		AND LEN(TbNotExists.actWBS) > LEN(TbActivite.actWBS)
	 )
 
 
-- Afficher la table temporaire pour valider le résultat avant une mise à jour
SELECT * 
FROM @ExpInvalide

Le problème avec cette requête, c'est que j'ai toujours les erreurs suivantes :

Code :

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

1
2
3
4
 
-> The multi-part identifier "TbActivite.actProjetCode" could not be bound.
-> The multi-part identifier "TbActivite.actWBS" could not be bound.
-> Invalid column name 'actWBS'.

Je ne saisie pas trop pourquoi les champs actProjetCod et actWBS de l'alias TbActivite ne peuvent être trouvé ! Sinon, je crois que je serais sur le bon chemin ?

**Sergejack** · 17/09/2009, 15h33

Il faudrait que tu nous exposes comment l'opération doit se comporter quand plusieurs expérimentations différents sont attribués à plusieurs niveau supérieur d'une même brache d'activité.

Ex, branche :
1.2.3.4.7.12.52

En rouge, des activités avec expérimentation.

**Erakis** · 18/09/2009, 04h32

En fait, lorsqu'il y a au moins une expérimentation attribuée à une activitée, celle-ci devient la dernière de la branche. Donc plus moyen d'ajouter une sous-activitée à cette branche.

Ex :

Code :

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

1
2
3
4
5
6
7
8
9
10
Projet 1                                                        
 |---Activite 1                                                           
	 |---Activite 1.1                                           
		 |---Activite 1.1.1                                     
		  	  |- Expérimentation
		 	  |- Expérimentation
		 |---Activite 1.1.2                                     
		 |---Activite 1.1.3                                     
		               |---Activite 1.1.3.1                                     
		               |-- Expérimentation     // ** ILLÉGAL **

Là ou s'est inscrit que c'est "// ** ILLÉGAL **" il faudrait que cette expérimentation soit déplacer dans "Activite 1.1.3.1".

Jusqu'ici, voilà ce que j'ai réussi à faire comme requête. Elle affiche toutes les activitées ayant des sous-activitées et au moins une expérimentations, ce qui est ici illégal et par conséquent les expérimentations doivent donc être déplacées dans une sous-activitée (de la même branche) mais qui n'a pas d'expérimentation :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT expCode, actCode, actProjetCode, actWBS
FROM Activite AS Tb1
     INNER JOIN Experimentation ON actCode = expActCode
WHERE EXISTS
     (
        SELECT actCode 
        FROM Activite AS TbNotExists 
        WHERE TbNotExists.actProjetCode = Tb1.actProjetCode 
              AND SUBSTRING(TbNotExists.actWBS, 1, LEN(Tb1.actWBS)) = Tb1.actWBS
              AND LEN(TbNotExists.actWBS) > LEN(Tb1.actWBS)
     )

J'ai aussi conçu une procédure stockée qui elle fait le travail désiré:

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
 
/*
   Création d'une table temporaire afin de stocker les expérimentation 
   situées dans une activitée n'était pas la dernière de la 
   branche/hiérarchie, ainsi que le code d'activité où ils devraient être 
   localisées.
*/
DECLARE @TmpExpInvalide TABLE 
(
    Tmp_expCode INT,
    Tmp_actCode INT,
    Tmp_actWBS NVARCHAR(40),
    Tmp_actProjetCode INT,
    Tmp_DeplacerDansSousActivite INT
)
 
/* 
   Trouver les expérimentations qui ne sont pas dans la dernière 
   branche d'une activité
*/
INSERT INTO @TmpExpInvalide(Tmp_expCode,Tmp_actCode, Tmp_actWBS, Tmp_actProjetCode)
SELECT TbExp.expCode, TbActivite.actCode, TbActivite.actWBS, TbActivite.actProjetCode 
FROM Activite AS TbActivite
	 INNER JOIN Experimentation AS TbExp ON TbActivite.actCode = TbExp.expActCode
WHERE EXISTS
	 (
		SELECT actCode 
		FROM Activite AS TbNotExists 
		WHERE TbNotExists.actProjetCode = TbActivite.actProjetCode 
				AND SUBSTRING(TbNotExists.actWBS, 1, LEN(TbActivite.actWBS)) = TbActivite.actWBS
				AND LEN(TbNotExists.actWBS) > LEN(TbActivite.actWBS)
	 )
 
/* 
   Mise à jour du code dans la table temporaire, de l'activité 
   où devrait plutot se trouver l'expérimentation
*/
UPDATE @TmpExpInvalide
SET Tmp_DeplacerDansSousActivite = 
   (
      SELECT TOP 1 actCode 
      FROM Activite AS q 
      WHERE q.actProjetCode = Tmp_actProjetCode 
            AND q.actWBS LIKE (Tmp_actWBS  + '%') 
      ORDER BY LEN(q.actWBS) DESC
   )
 
/*
   Mise à jour de la position des expérimentation
*/
UPDATE Experimentation
SET expActCode = TmpTable.Tmp_DeplacerDansSousActivite
FROM @TmpExpInvalide AS TmpTable
WHERE expCode = TmpTable.Tmp_expCode

Voilà... ça fait le travail, mais je ne crois pas que ce soit optimal.

Autre chose en passant. Comment appel ton ce genre de requête ?

Code :

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

1
2
3
4
5
6
7
8
9
   SELECT A
   FROM Table1 AS Tb1
            INNER JOIN
            (
                 SELECT MAX(A) AS MaxA
                 FROM Table 1   
            )
            AS Tb2 ON Tb1.A = Tb2.MaxB

Peut-on appeler cela une sous requête anonyme ? Puisque la jointure est fait sur une table qui est produit à la volé (À partir du résultat du select) et non sur une table réelle. Y a-t-il un nom spécifique pour cela ? (Anglais/français)

Merci pour votre aide

**Sergejack** · 18/09/2009, 09h16

Au début tu parlais de mettre à jour ton arbre et maintenant tu parles d'interdire les défauts de sa structure.

Je ne comprends pas vraiment ce que tu veux corriger ni comment.

Pour virer toutes les expérimentations situés à des noeuds qui ne sont pas des feuilles (non terminal donc) tu peux utiliser la logique suivante.

Code :

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

1
2
3
4
5
6
7
créer liste de tous les noeuds (DECLARE @... TABLE ... + INSERT...)
pour lequel il existe un autre noeud dont le code est une extension (LIKE <code de base> + '%') du premier noeud considéré.

supprimer l'expérimentation
pour tout noeud
présent dans la liste

Ça t'aide ?

**Erakis** · 23/09/2009, 15h22

Oui en fait, c'est ce que la requête que j'ai inscrit précédemment fait ? Mais est-ce correct ?

Le fait est que ce n'est pas moi qui a fait cette base de données. En ce moment, j'ai le mandat de réviser et améliorer le projet. J'ai remarqué qu'il y a plusieurs incohérences dans cette base de données. Celle-ci en est qu'une pour ne pas tous les nommer. Bref, je dois les corriger tous avant de faire évoluer ce projet à une autre étape.

J'ai une autre question par rapport à la hiérarchie.

Sachant qu'on parle souvent dans les livres d'éviter la redondance de données. Je me pose la question suivante par rapport à la structure de mes tables.

Voici les deux tables sur lesquelles repose ma question :

Activité

Code :

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

1
2
3
4
5
6
7
8
9
actCode,   actWBS,    actProjetCode
  258	     1.	         45
  259	     2.	         45
  260	     3.	         45
  261	     1.1.	 45
  262	     1.2.	 45
  263	     1.3.	 45
  264	     1.1.1.      45
  265	     1.1.1.1.    45

Expérimentation

Code :

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

1
2
3
expCode, expActCode,     expDateDebut,               expDateFin
  1	   261         2009-09-22 10:00:00      2009-09-22 11:00:00
  2        265         2009-09-23 13:00:00      2009-09-22 14:00:00

Donc, pour chaque acticité, il y a 0 ou plusieurs expérimentations effectuées basés sur une date de début et de fin. La somme de toute les expérimentation (expDateFin - expDateDebut) pour une activitée donnera le temps passé sur l'activitée en question.

Dans mon application web, il me faut récupérer la liste des projets, qui consiste en une liste de toute les activitées du système et y afficher pour chacune, la somme des heures (table expérimentation) qui on été fait pour chaque activité.

Les activitées qui doivent être affichée, sont seulement ceux étant la dernière de la branche. Donc en d'autre mot ; les activitée n'étant pas le parent d'aucune autre activitée. Et seule ceux-ci peuvent contenir des temps d'expérimentations.

Afin de trouver les dernières activités de la branche, je dois faire une sous-requête et balayer mon "actWBS" avec un LIKE, ce qui à mon avis est assez dispendieux en performance, puisque c'est une opération (LIKE) sur une STRING. Quand il y a aura 50 000 activités, ce sera assez notable!

Pour palier à ce problème, j'ai pensé ajouté un champs à ma table activitée (actParentCode), qui consisterait à spécifier le code de l'activité parent affecté à une activité. Ensuite, lorsque je voudrais récupérer les activitée n'étant parent d'aucune autre, j'aurais qu'à faire quelque chose du genre :

Code :

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

1
2
3
4
SELECT ...
FROM Activite AS TabActiviteParent
     LEFT JOIN Activite AS TabActiviteEnfant ON TabActiviteParent.actCode = TabActiviteEnfant.actParentCode
WHERE TabActiviteEnfant.actParentCode IS NULL

Ce qui serait beaucoup plus rapide (jointure VS sous-requête). Toutefois, peut-on dire que c'est de la redondance puisque nous pouvons déduire cette même information avec le champs "actWBS" ? Ou c'est passable, puisque c'est dans une optique de performance ?

Merci pour vos réponses
Martin

Récupérer la chaine la plus longue

Développement SQL Server

Discussions similaires

Partager

Partager