Discussion :

[PickEpique]

J'ai de gros problème de compréhension du sql, par exemple pour la table autoref suivante représentant un arbre je voudrais faire un update
sur celle ci du genre suivant

Update Test_CTE
champs = aggrégation sur lignes enfants
pour des lignes (ou enregistrements ?)
1 qui n'ont pas déja été updatés par cette requête
2 qui sont des parents
3 dont tous les enfants ont été updatés par cette 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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_CTE]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Test_CTE](
		[ID] [bigint] NOT NULL,
		[PID] [bigint] NULL,
                          [STYPE] [int] NULL,
		[NOM] [varchar](50) COLLATE French_CS_AS NULL,
		[PU] [numeric](18, 0) NULL
                          [FCALC] [int] NULL
	) ON [PRIMARY]
END;
 
DELETE FROM Test_CTE;
 
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (1, NULL, 0 ,'ELEMENT 1', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (2, 1, 0 ,'ELEMENT 2', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (3, 1, 0 ,'ELEMENT 3', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (4, 2, 0 ,'ELEMENT 4', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (5, 2, 0 ,'ELEMENT 5', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (6, 3, 0 ,'ELEMENT 6', 10, 1)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (7, 3, 0 ,'ELEMENT 7', 0)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (8, 6, 0 ,'ELEMENT 8', 0)
INSERT INTO Test_CTE (ID, PID, STYPE, NOM, PU) VALUES (9, 6, 0 ,'ELEMENT 9', 0)
 
SELECT T1.ID AS ID_PARENT, T2.ID AS ID_ENFANT , T2.NOM FROM Test_CTE AS T1 INNER JOIN Test_CTE AS T2 ON (T1.ID = T2.PID)
 
 
SELECT * FROM Test_CTE
WHERE	EXISTS ( SELECT * FROM Test_CTE AS T1
				 WHERE T1.PID = ID )
 
SELECT * FROM Test_CTE
WHERE	EXISTS ( SELECT * FROM (SELECT T1.ID AS ID_PARENT, T2.ID AS ID_ENFANT , T2.NOM 
								FROM Test_CTE AS T1 INNER JOIN Test_CTE AS T2 ON (T1.ID = T2.PID)) AS T1
				 WHERE T1.ID_PARENT = ID )

tout d'abord je ne comprend pas très bien que pour ma condition 2
la requète 2 ne fonctionne pas, et qu'il faille la troisième pour cela

La procedure que je veux obtenir est de ce genre la

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
 
/*mise à 0 de tout les éléments qui sont des parents */
/* puis */
	WHILE ((SELECT COUNT(*) FROM Test_CTE WHERE CALCF = 0 ) > 0)
	BEGIN
		UPDATE @Test_CTE
			SET 
			P.CALCF = 1,
			P.PU = PA.PT
 
		FROM
			Test_CTE AS P
				INNER JOIN 
					   (SELECT 
							A2.ID AS ID_P,
							COUNT(A1.ID) AS N_E,
							SUM(A1.PU) AS PT,
						FROM Test_CTE AS A1 
							INNER JOIN
								@Test_CTE AS A2 ON (A1.PID = A2.ID)
						GROUP BY A2.ID) 
						AS PA ON (P.ID = PA.ID_P)
		WHERE  
			P.CALCF = 0
			AND P.STYPE <> 4  /* pour simplifier la recherche 2 */
			AND NOT EXISTS (SELECT * FROM (	SELECT T2.ID AS ID_PARENT, T1.ID AS ID_ENFANT, T1.CALCF AS CALCF
											FROM Test_CTE AS T1 
												INNER JOIN
												Test_CTE AS T2 ON (T1.PID = T2.ID)
										   ) AS F1 
							WHERE F1.PID = P.ID AND F1.CALCF = 0)
	END

cette ps fonctionne mais est beaucoup trop longue d'execution
sur des données plus importantes que l'échantillon, quelqu'un a t'il une meilleure idée, cette ps fonctionne mais avec des curseurs, j'aimerais pouvoir
faire sans SVP

[PickEpique]

toujours pour le même problème de fonction d'aggrégation dans une arborescence la ps suivante fonctionne mais est 2 à 3 fois plus longue que sensiblement la même avec un curseur dans la boucle while (plusieurs itérations) le pb est sommé les éléments de cette arborescence seulement quand les enfants de cet éléments sont déja sommé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
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
 
ALTER PROCEDURE [dbo].[USP_CALC_DEVIS_MOD1] @P_DEV_ID BIGINT
AS
BEGIN
 
SET NOCOUNT ON;
 
 
 
DECLARE @DevisPartTmp TABLE(
		ID BIGINT PRIMARY KEY,
		PID BIGINT,
		STYPE INT,
		FAMILLE VARCHAR(16),
		SFAMILLE VARCHAR(16),
		QTE NUMERIC(15,4),
		PUA1 NUMERIC(15,4),
		PUA2 NUMERIC(15,4),
		PUV1 NUMERIC(15,4),
		PUVF NUMERIC(15,4),
		PTA1 AS QTE * PUA1,
		PTA2 AS QTE * PUA2,
		PTV1 AS QTE * PUV1,
		PTVF AS QTE * PUVF,
		CALCF INT
	)
 
	INSERT INTO @DevisPartTmp
	SELECT A.DPT_ID, A.DPT_PID, A.DPT_STYPE, A.DPT_FAMILLE, A.DPT_SFAMILLE, A.DPT_QTE, A.DPT_PUA1, A.DPT_PUA2, A.DPT_PUV1, A.DPT_PUVF, DPT_CALCF
	FROM T_DEVPART_DPT A
	WHERE A.DEV_ID = @P_DEV_ID
 
    UPDATE @DevisPartTmp 
	SET 
		PUA1 = 0,
		PUA2 = 0,
		PUV1 = 0,
		PUVF = 0,
		CALCF = 0
	WHERE STYPE <> 4
 
	WHILE ((SELECT COUNT(*) FROM @DevisPartTmp WHERE CALCF = 0 ) > 0)
	BEGIN
		UPDATE @DevisPartTmp
			SET 
			P.CALCF = 1,
			P.PUA1 = (SELECT SUM(S1.PTA1) FROM @DevisPartTmp S1 WHERE S1.PID = P.ID), 
			P.PUA2 = (SELECT SUM(S1.PTA2) FROM @DevisPartTmp S1 WHERE S1.PID = P.ID), 
			P.PUV1 = (SELECT SUM(S1.PTV1) FROM @DevisPartTmp S1 WHERE S1.PID = P.ID), 
			P.PUVF = (SELECT SUM(S1.PTVF) FROM @DevisPartTmp S1 WHERE S1.PID = P.ID) 				
		FROM
			@DevisPartTmp AS P				
		WHERE  
			P.CALCF = 0
			AND P.STYPE <> 4 
			AND NOT EXISTS (SELECT * FROM 
							(SELECT T2.ID AS ID_P, T1.ID AS ID_E, T1.CALCF 
							 FROM @DevisPartTmp T1 INNER JOIN @DevisPartTmp T2 ON (T1.PID = T2.ID) )
							AS F1 WHERE F1.ID_P = P.ID AND F1.CALCF = 0)
	END
 
    SET NOCOUNT OFF;
 
	SELECT * FROM @DevisPartTmp
 
END