[Transac SQL] double récursivité ?

[yozart]

Bonjour à tous,

je fais face à un problème que je n'arrive pas à résoudre. Voici la table est les données que j'utilise pour tester:

Code :

1
2
3
4
5
6
7
8
9
10
 
 
CREATE TABLE test
(
	COMMANDE int NOT NULL,
	COLIS int  NOT NULL,
 CONSTRAINT PK_test PRIMARY KEY CLUSTERED (COMMANDE ASC, COLIS ASC) 
);
 
INSERT INTO test(COMMANDE,COLIS) VALUES (5001,8001),(5002,8001),(5002,8002),(5001,8003),(5003,8003),(5003,8004),(5004,8004),(5005,8004),(5005,8005),(5005,8006)

Dans cette table, nous associons des commandes à des colis. Cependant, une commande peut être composée de plusieurs colis et un colis peut contenir plusieurs commandes.


Je souhaiterais pouvoir récuperer toutes les commandes liées de près ou de loin à un colis passé en paramètre.

Pour ce faire, j'ai trouvé la page suivante: http://msdn.microsoft.com/fr-fr/library/ms175972.aspx

Cette page semble poser les bases de mon problème mais j'ai l'impression d'avoir besoin d'une double recursivité.

voici ce que j'ai essayé de faire sans succès:

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
34
35
 
 
USE ma_bdd;
GO
WITH DirectReports (COMMANDE, COLIS)
AS
(
-- Anchor member definition
    SELECT d.COMMANDE, d.COLIS
    FROM test AS d
    UNION ALL
    SELECT d.COMMANDE, d.COLIS
    FROM test AS d
    INNER JOIN revertReports AS e
        ON d.COMMANDE = e.COMMANDE)
 
GO
WITH revertReports (COMMANDE, COLIS)
AS
(
-- Anchor member definition
    SELECT d.COMMANDE, d.COLIS
    FROM test AS d
    UNION ALL
    SELECT d.COMMANDE, d.COLIS
    FROM test AS d
    INNER JOIN DirectReports AS e
        ON d.COLIS = e.COLIS
)
 
-- Statement that executes the CTE
SELECT COMMANDE, COLIS
FROM DirectReports
WHERE COLIS = 8006;
GO

En esperant que vous pourrez m'aider à surmonter cela, Merci.

[Waldar]

Si j'ai bien compris :

Code :

1
2
3
4
5
6
SELECT T1.commande, T1.colis 
  FROM test AS T1
 WHERE EXISTS (SELECT NULL
                 FROM test AS T2
                WHERE T2.commande = T1.commande
                  AND T2.colis = 8006)

[yozart]

Merci mais malheureusement cela ne convient pas.

La requete ne retourne que les résultats suivants:

Code :

1
2
3
4
 
5005 - 8004
5005 - 8005
5005 - 8006

dans l'exemple donné, je devrais retrouver tous les colis de 8001 a 8006 car :

colis 8006 contient la commande 5005 qui comporte egalement les colis 8004 et 8005,

le colis 8004 contient egalement les commandes 5003 et 5004 qui elles même comportent le colis 8003.

Le colis 8003 contient egalement la commande 5001 qui......

[punkoff]

Bonjour,

comment reconnait-on la commande mère ?

edit : en fait je ne saurai pas faire, il manque une relation sur la commande mère .. et la vous n'auriez plus de problème pour retrouver vos petits

[yozart]

Bonjour,

tout le problème est la. il n'y a pas de commande mère à proprement parler.

Ce n'est pas une hiérarchie comme vous pouvez le voir sur le schéma (il n'y a pas de chef en dessous duquel viennent des subordonnés)

Il faudrait qu'a partir de n'importe quel colis d'entré, je puisse retrouver toutes les commandes associées

[punkoff]

une piste mais c'est moche .... il faudrai pouvoir limité l'effet itératif, et ca je ne sais pas trop comment le faire.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH recursive tmp(commande, colis, com_mere, level) AS 
(
SELECT commande, colis, commande, 0
FROM t_comm
WHERE commande = 5001
union ALL
SELECT b.commande, b.colis, b.commande, a.level + 1
FROM tmp a
INNER JOIN t_comm b ON (a.commande <> b.commande AND a.colis = b.colis) OR (a.com_mere = b.commande AND a.colis <> b.colis)
WHERE a.level < 5)
 
SELECT DISTINCT commande, colis
FROM tmp

[vmolines]

Pas sûr mais je pense que c'est bon :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH CommandeLiee (COM_ID, COL_ID) 
AS (
SELECT CC.CC_COM_ID, CC.CC_COL_ID
FROM COMMANDE_COLIS CC
WHERE CC.CC_COL_ID = 8006
UNION ALL 
SELECT CC2.CC_COM_ID, CC2.CC_COL_ID
FROM CommandeLiee 
	INNER JOIN COMMANDE_COLIS CC1
		ON CC1.CC_COM_ID = CommandeLiee.COM_ID
		AND CC1.CC_COL_ID <> CommandeLiee.COL_ID
		INNER JOIN COMMANDE_COLIS CC2 
			ON CC1.CC_COL_ID = CC2.CC_COL_ID 
			AND CC2.CC_COM_ID <> CommandeLiee.COM_ID
 
)
 
SELECT COM_ID 
FROM CommandeLiee

Avec ceci comme instructions pour mettre en place le jeu d'essai (c'était pas nécessaire mais la réécriture m'a aidé pour une bonne compréhension) :

Code :

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 COLIS
(COL_ID int NOT NULL PRIMARY KEY
);
 
CREATE TABLE COMMANDE
(COM_ID int NOT NULL PRIMARY KEY
);
 
 
 
CREATE TABLE COMMANDE_COLIS
(
	CC_COM_ID int NOT NULL,
	CC_COL_ID int NOT NULL,
 CONSTRAINT PK_test PRIMARY KEY CLUSTERED (CC_COM_ID ASC, CC_COL_ID ASC), 
 CONSTRAINT FK_COM FOREIGN KEY (CC_COM_ID) REFERENCES COMMANDE (COM_ID),
 CONSTRAINT FK_COL FOREIGN KEY (CC_COL_ID) REFERENCES COLIS (COL_ID)
);
 
INSERT INTO COMMANDE (COM_ID) VALUES (5001), (5002), (5003), (5004), (5005); 
INSERT INTO COLIS (COL_ID) VALUES (8001), (8002), (8003), (8004), (8005), (8006);
 
INSERT INTO COMMANDE_COLIS(CC_COM_ID,CC_COL_ID) VALUES (5001,8001),(5002,8001),(5002,8002),(5001,8003),(5003,8003),(5003,8004),(5004,8004),(5005,8004),(5005,8005),(5005,8006);

Le point de départ est bien un numéro de colis à passer en paramètre au niveau de l'ancre.

[yozart]

Merci beaucoup à vous tous.

vmolines, ta solution semble effectivement parfaite. c'est impressionnant. J'ai vraiment du mal a comprendre le fonctionnement.

En tous cas, merci milles fois. vous me sauvez...

[vmolines]

La requête cherche les commandes associées à un colis. Et pour chaque commande retournée, on cherche les commandes associées aux colis de la commande (la récursivité est là en passant par les colis de chaque commande). Ensuite j'évite de repasser sur les mêmes chemins à savoir :

- éviter le colis qui m'a permis d'amorcer la recherche
- éviter de retourner une commande déjà traitée

[punkoff]

Donc au final vous n'aviez pas besoin de ça ? :

dans l'exemple donné, je devrais retrouver tous les colis de 8001 a 8006 car :

Là le colis 8002 ne remonte pas

[yozart]

c'est ce que j'avais cru comprendre en décortiquant le code. Merci beaucoup. Je vais garder bien précieusement cet exemple.

Je pense également que ce code va servir a plus d'une personne !

Merci encore.

[yozart]

Bonjour,

je reviens a l'assaut car malheureusement, je viens de tomber sur un cas qui pose a priori problème :

Code :

1
2
3
4
5
6
7
8
9
10
 
INSERT INTO COMMANDE (COM_ID) VALUES (52173035), (52173047), (52173038), (52173044), (52173048); 
INSERT INTO COLIS (COL_ID) VALUES (800821413), (800821373);
INSERT INTO COMMANDE_COLIS(CC_COM_ID,CC_COL_ID) VALUES (52173035,	800821373),
(52173038,	800821373),
(52173044,	800821373),
(52173047,	800821373),
(52173048,	800821373),
(52173035,	800821413),
(52173047,	800821413)

je pense que le soucis viens du fait que les 2 colis contiennent deux même commandes:

52173047 et 52173035

Pour le moment je n'ai pas reussi a modifier le code pour corriger cela. Si vous avez une solution, je vous en serai très reconnaissant.

[punkoff]

Envoyé par punkoff

Donc au final vous n'aviez pas besoin de ça ? :

Là le colis 8002 ne remonte pas

Quid de ma précédente intervention ?

[yozart]

Oups désolé,

Je n'avais pas vu ta réponse. On a du se croiser :s

As tu une idée de correctif?

[punkoff]

bah actuellement je ne comprend pas ton besoin car tu répond X à Waldar et Y à Vmolines.
Donc reprend le topic fait des tests en conséquents et détermine précisément ton besoin.

[yozart]

Ok, désolé, ce n'est pas forcément très clair, je le conçois. Je vais donc faire une nouvelle tentative.

Je veux récupérer la liste complète des numéros de commandes liées de près ou de loin à un numéro de colis.

Donc, en gros, que je ne retrouve pas le colis 8002 n'est pas grave en soit tant qu'il n'y a pas d'autre commande dessus.

(Je sais pas si c'est beaucoup plus clair :s)

[punkoff]

ok, donc par rapport à ton graphe si on selectionne n'importe quelle commande en entrée (ou colis) on doit se retrouver avec la liste de commande suivante : 5001, 5002, 5003, 5004, 5005 ?

edit :
Je ne sais pas si ms sql gère les cycles "sans fin".

Mais en restant sur la 1ere requete recursive fournit ca fait le job. ré-adaptée car j'avais fait un peu n'imp avec la commande mère (elle ne sert à rien)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH recursive tmp(CC_COM_ID, CC_COL_ID, level) AS 
(
SELECT CC_COM_ID, CC_COL_ID, 0
FROM COMMANDE_COLIS
WHERE CC_COM_ID = 52173038
union ALL
SELECT DISTINCT b.CC_COM_ID, b.CC_COL_ID, a.level + 1
FROM tmp a
INNER JOIN COMMANDE_COLIS b ON (a.CC_COM_ID <> b.CC_COM_ID AND a.CC_COL_ID = b.CC_COL_ID) OR (a.CC_COM_ID = b.CC_COM_ID AND a.CC_COL_ID <> b.CC_COL_ID)
WHERE a.level < 10)
 
SELECT DISTINCT CC_COM_ID
FROM tmp

le "level" sert à limiter la recurssion vu qu'on est dans un cas infinit. Il faut juste prendre une valeur assez grande pour pouvoir iterer sur toutes les comandes.
Idéallement il faudrait pouvoir changer ceci avec une option qui permetrai de ne pas cycler sur une ligne déjà traitée. (à voir avec les sépcialistes ms sql)

[yozart]

Merci Punkoff,


comme tu le dis, l'idéal serait de trouver un moyen de ne pas boucler sur les Commandes déjà processées.

Avec ton systeme de level, il est peut être possible de l'utiliser comme un flag non ? si commande déjà traitée, je passe le flag a 1 (par defaut a zero). Je sais pas si c'est possible mais c'est clairement au dessus de mes compétences. j'ai passé 3 heures à tester diverses choses sans succès...

[punkoff]

Le problème c'est que la table temporaire ne peut pas être utilisée en sous-requête. Du coup c'est assez limité.. sinon ca aurai été bouclé avec un NOT EXISTS ou un group by sur le couple + max(level)

[yozart]

oui, c'est ce que j'avais essayé sans succès