Impasse requête help [Résolu]

[lead8209]

Bonjour,
je me permets de poster car je n'arrive pas à écrire une requête...

Voici mes tables :

Actions[id,libelle]
-----------------
1 Ac1
2 Ac2
3 Ac3
----------------

Articles[id,libelle]
-----------------
1 Ar1
2 Ar2
3 Ar3
........
9 Ar9
10 Ar10
11 Ar11
12 Ar12
----------------

ArticlesSimilaires[id,id_groupe,id_Article*]
-----------------
1 1 1
2 1 9
3 1 10
4 2 2
5 2 12
6 3 13
----------------
Il faut lire l'article 1 est similaire aux article 9 et 10. De même l'article 2 et similaire à article 12 et 13.

RegroupementActionsArticles[id,id_Action*,id_Article*]
-----------------
1 1 1
2 1 3
3 2 2
4 2 3
5 3 3
6 3 3
----------------
Il faut lire l'action 1 (Ac1) peut-être appelée avec exactement la combinaison d'article 1 et 3 (ou 9 et 3 ou 10 et 3 avec la correspondance article similaire).

Le but de la requête est de donner les Actions à partir d'une combinaison d'articles sachant que certains articles peuvent être remplacés par un article "similaire".

Ex 1:
J'ai les articles Ar1 et Ar3 qu'elle action lui correspond ? réponse l'action 1.
Ex 2:
J'ai les articles Ar1 et Ar9 qu'elle action lui correspond ? réponse aucune !
Ex 3:
J'ai les articles Ar9 et Ar3 qu'elle action lui correspond ? réponse l'action 1.

D'avance merci. Si besoin de précisions ne pas hésiter...

[aieeeuuuuu]

Bonjour

est-ce que les combinaisons peuvent être composées de plus de deux articles ?
ou à l'inverse, d'un seul article ?

[lead8209]

Effectivement on peut trouver dans la table RegroupementAA plusieur fois le même article pour une action.

RegroupementActionsArticles[id_Action,id_Article]
Ci-dessous une visualisation de la table enrichie (avec import des libellés correspondants aux clés étrangères id_Article et id_Action grâce à un join).

[id_Action][Action.libellé] [id_Article][Article.libelle]
----------------------------------------------------------------------------------
1 Création d"un fuseau 1 FILM12µm
1 Création d"un fuseau 3 Ruban
2 Création d"un assemblage de fuseau 4 Fuseau
2 Création d"un assemblage de fuseau 3 Ruban
2 Création d"un assemblage de fuseau 4 Fuseau
3 Ajout d"un fuseau à un assemblage 5 Assemblage
3 Ajout d"un fuseau à un assemblage 3 Ruban
3 Ajout d"un fuseau à un assemblage 4 Fuseau
4 Création d"un assemblage de fuseau 5 Assemblage
4 Création d"un assemblage de fuseau 3 Ruban
4 Création d"un assemblage de fuseau 5 Assemblage
----------------------------------------------------------------------------------


J'ai cependant trouvé une solution en couplant une requête plus basique à mon programme. Le détail à suivre dans le prochain post.
Si vous trouver une requête qui traite ma fonction d'un seul coup je suis bien entendu preneur.

[aieeeuuuuu]

et donc, il faut que la requete soit en mesure de repondre à la question :

J'ai les articles 5, 3 et 4, quelle actions ? => l'action 3
(selon le dernier exemple)

Mais ne reponde rien si on a seulement les articles 5 et 4 ?

et si on a seulement les articles 5 et 4, mais que 4 est similaire a 3, que doit-elle répondre ?

Votre exemple est relativement clair, mais j'ai l'impression que le besoin est plus général...

Pouvez vous expliquer un peu le contexte, ca aidera peut etre a comprendre

D'autre part, d'ou proviendra la combinaison des articles disponibles ?
(le but est-il de voir toutes les actions possibles en fonction de votre stock ? lequel se trouve dans une autre table ?)

[lead8209]

Ma méthode (on prendra les données de la mise en situation 1er post)

Utilisateur entre une liste d'article (9 et 3)
Fonction de création des combinaisons possibles donnes 3 listes :
1-3
9-3
10-3



POUR chaque combinaisons d'article

Code :

1
2
3
4
5
6
7
8
9
10
11
12
{
On sélectionne dans la table de regroupement les id_Actions qui utilisent au moins un article dans la combinaison.
SELECT id_Action FROM RegroupementActionArticle WHERE id_Article = 1
INTERSECT
SELECT id_Action FROM RegroupementActionArticle WHERE id_Article = 3

Problème vont aussi être retournées toutes les actions qui utilisent au moins 1 article du type id=xxx. Dans le cas ou l'on passe la combinaison d'articles (1, 1, 3) on aura également l'action(article1,article3)... On est incapable de dénombrer qu'il y a exactement 1 article 1 et 1 article 3... bref  
POUR chaque id_Action on sélectionne dans la table de regroupement la liste des id_Articles
{
On regarde avec une double boucle que les articles entrés par l'utilisateur sont présents dans les articles retournés par la sélection de l'action.
On sauvegarde le résultat vrai/faux dans une liste. Si vrai on ajoute également l'id_Action.
}

}

Boucle sur la liste vrai/faux on regarde les résultats...si on a au moins un vrai (techniquement c'est aucun ou un) c'est notre action.

[lead8209]

Oui tout à fait. Si on n'a pas exactement les articles 5 3 et 4 elle requete doit renvoyer vide.

Explication du contexte.
Cette fonction est développée dans le cadre d'un outil de production de pièces usinées afin de tracer les numéro de série.
La dite fonction doit permettre de trouver les actions qui correspondent à une liste d'articles. Les articles étant renseignés par l'utilisateur.
Exemple 1:
l'utilisateur sélectionne 2 fuseaux et du ruban et le système me propose "création d'un assemblage".
Exemple 2 :
utilisateur sélectionne 1 FILM 15µ et du ruban or la table de correspondance regroupementActionArticle ne connait que 1 FILM 12µ + 1 ruban = "Création d'un fuseau". Mais elle sait également qu'elle peut remplacer le FILM 15µ par du FILM 12µ (grâce à la table ArticleSimilaires)

Cette fonction doit permettre de proposer des actions à l'utilisateur mais également d'empécher l'administrateur de créer une action"Création d'un fuseau" avec la combinaison FILM 15µ + Ruban car nous avons déjà la combinaison FILM 12µ + Ruban et que FILM12µ et FILM15µ sont dits similaires.

[aieeeuuuuu]

Quelle est votre version de SQL Server ?

[lead8209]

SQL SERVEUR 2008 R2 vEXPRESS mais 2011 v standard lors du déploiement chez le client.

[aieeeuuuuu]

une autre question également :

Il faut retrouver les actions pour lesquelles tous les articles sont présent dans la liste. Mais si certaines dans la liste ne sont pas necessaire pour une action, doit-elle est slectionnée quand meme par la requete ?

par exemple, vous dites :

J'ai les articles Ar1 et Ar3 qu'elle action lui correspond ? réponse l'action 1.

est-ce que l'action 1 doit également être trouvée si les articles Ar1, Ar3 et Ar12 sont donnés ?

[lead8209]

Non l'action ne doit pas être sélectionnée.
La sélection d'un action se fait uniquement s'il y a le nombre exact d'articles.
Soit l'action A_x composée des articles Ar_w,Ar_x et Ar_y.
Soit l'article Ar_z ne faisant partit d'aucune action.
Soit l'article Ar_wBis similaire de l'article Ar_w.
Si l'utilisateur passe en paramètre Ar_w,Ar_x ,Ar_y Ar_z le système retourne null.
Quelques cas et le résultat associé :

Ar_w,Ar_x --> null il manque Ar_y
Ar_w,Ar_w,Ar_y --> null il manque Ar_y et on a un Ar_w en trop
Ar_w,Ar_w,Ar_x ,Ar_y -->null n a un Ar_w en trop
Ar_w,Ar_x ,Ar_y --> A_x ok tout y est
Ar_x,Ar_w,Ar_y --> A_x tout y est même si le panel n'est pas dans l'ordre
Ar_x,Ar_wBis,Ar_y -->A_x tout y est car même s'il manque Ar_w; Ar_wBis et dans le panel.

En gros il faut exactement les bon articles, le bon nombre, certains peuvent être échangés et l'ordre d'ajout n'a aucune importance.

[aieeeuuuuu]

Est-ce que cette requete répond au besoin (et surtout aux différents cas particuliers ?)

Code SQL :

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
 
;WITH Actions AS(--jeu d'essai
	SELECT 1 AS id, 'Act1' AS libelle
	UNION ALL SELECT 2, 'act2'
	UNION ALL SELECT 3, 'act3'
),
Articles AS (--jeu d'essai
	SELECT 1 AS id, 'ar1' AS libelle
	UNION ALL SELECT 2, 'ar2'
	UNION ALL SELECT 3, 'ar3'
	UNION ALL SELECT 4, 'ar4'
	UNION ALL SELECT 5, 'ar5'
	UNION ALL SELECT 6, 'ar6'
	UNION ALL SELECT 7, 'ar7'
	UNION ALL SELECT 8, 'ar8'
	UNION ALL SELECT 9, 'ar9'
	UNION ALL SELECT 10, 'ar10'
	UNION ALL SELECT 11, 'ar11'
	UNION ALL SELECT 12, 'ar12'
	UNION ALL SELECT 13, 'ar13'
	),
ArticlesSimilaires AS (--jeu d'essai
	SELECT 1 AS id, 1 AS id_groupe, 1 AS id_article
	UNION ALL	SELECT 2,1,9
	UNION ALL	SELECT 3,1,10
	UNION ALL	SELECT 4,2,2
	UNION ALL	SELECT 5,2,12
	UNION ALL	SELECT 6,3,13
	),
regroupementAA AS (--jeu d'essai
	SELECT 1 AS id, 1 AS id_action, 1 AS id_article
	UNION ALL SELECT 2,1,3
	UNION ALL SELECT 3,2,2
	UNION ALL SELECT 4,2,3
	UNION ALL SELECT 5,3,3
	UNION ALL SELECT 6,3,3
	),
Liste AS (--jeu d'essai => liste des artices saisis
	SELECT 'ar3' AS Ar
	UNION ALL SELECT 'ar2'
	--UNION ALL SELECT 'ar3'
	--...
	),
ListeRN AS (-- numerotation des articles
	SELECT 
		A1.id AS id_article, 
		id_groupe,  
		ROW_NUMBER() OVER (ORDER BY A1.id) AS RN
	FROM Liste
	INNER JOIN Articles A1
		ON A1.libelle = Liste.Ar
	LEFT OUTER JOIN ArticlesSimilaires S1
		ON S1.id_article = A1.id
	),
ListeSimilaire AS (-- liste des articles "compatibles"
	SELECT 
		COALESCE(A2.id, L.id_article) AS id_article, 
		L.RN AS RN
	FROM ListeRN L
	LEFT OUTER JOIN ArticlesSimilaires S2
		ON S2.id_groupe = L.id_groupe
	LEFT OUTER JOIN Articles A2
		ON A2.id = S2.id_article
		),
RegroupementRN AS ( --numerotation des articles pour chaque action
	SELECT 
		id_action, id_article, 
		ROW_NUMBER() OVER (PARTITION BY id_action ORDER BY id_article) AS RN, 
		COUNT(*) OVER (PARTITION BY id_action) AS NbArticlesAction
	FROM regroupementAA
	),
Matching AS (--matching entre la liste saisie et les actions		
	SELECT DISTINCT 
		id_action,
		NbArticlesAction, 
		R.RN AS ArticleActionRN, 
		LS.RN AS ArticleListeRN
	FROM regroupementRN R
	INNER JOIN  ListeSimilaire LS
		ON R.id_article = LS.id_article
)
SELECT id_action
FROM Matching
WHERE NbArticlesAction = (SELECT COUNT(*) FROM Liste) --autant d'articles pour l'action que dans la liste
GROUP BY 
	id_action, 
	NbArticlesAction
HAVING 
	COUNT(DISTINCT ArticleActionRN) = NbArticlesAction --Chaque Article de l'action a une correspondance
	AND COUNT(DISTINCT ArticleListeRN) = (SELECT COUNT(*) FROM Liste) --chaque Article de la liste a une correspondance

[lead8209]

Bonjour,
heu oui ça à l'air de correspondre merci beaucoup pour le temps passé.
Par contre cela dépasse de loin mes compétences en SQL... et je n'ai aucune idée de comment l'appliquer à ma base...

J'ai compris qu'il y avait une première partie qui permet de simuler les tables existantes (Articles,Articles_similaires,Actions,regroupementActionAssemblage) une deuxième qui permet de créer des 'tables virtuelles' (liste des articles saisis etc (là je flanche)) et enfin la fonction qui s'appuie sur le tout.

Comment intégrer les tables existantes ? Un petit cours SQL serait le bienvenu

[aieeeuuuuu]

Envoyé par lead8209

J'ai compris qu'il y avait une première partie qui permet de simuler les tables existantes (Articles,Articles_similaires,Actions,regroupementActionAssemblage)

Tout à fait. Cette partie est à supprimer. remplacez les nom des pseudo tables par les vrais nom de vos tables dans la suite de la requete.

La table liste est a garder, c'est elle qui contient la liste "saisie par l'utilisateur". Comment comptez vous appeler la requête depuis le programme (requête appelée directement depuis le programme, procédure stockée,...). Vous pourrez éventuellement modifier cette partie en fonction de ça.

une deuxième qui permet de créer des 'tables virtuelles' (liste des articles saisis etc (là je flanche)) et enfin la fonction qui s'appuie sur le tout.

Cette deuxième partie est à laisser tel quel (bien que ça pourra être optimisé).
Pour comprendre le fonctionnement, vous pouvez remplacer la requête finale par un SELECT * sur les pseudo tables, afin de voir ce qu'elles contiennent...

Mais pour faire fonctionner la requête sur votre base, remplacez juste le nom des tables et des colonnes par les vrais noms (supprimez les pseudo tables pour le jeu d'essai) et ça devrait être bon

[Waldar]

J'ai une requête sans fonctions de fenêtrage ni sous-requête (sauf celle qui calcule les éléments saisis) :

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
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
-- Début de vos données
;WITH Actions (id_action, libelle) AS
(
SELECT 1, 'act1' UNION ALL
SELECT 2, 'act2' UNION ALL
SELECT 3, 'act3'
)
   ,  Articles (id_article, libelle) AS
(
SELECT  1, 'ar1'  UNION ALL
SELECT  2, 'ar2'  UNION ALL
SELECT  3, 'ar3'  UNION ALL
SELECT  9, 'ar9'  UNION ALL
SELECT 10, 'ar10' UNION ALL
SELECT 11, 'ar11' UNION ALL
SELECT 12, 'ar12' UNION ALL
SELECT 13, 'ar13'
)
   ,  ArticlesSimilaires (id_groupe, id_article) AS
(
SELECT 1,  1 UNION ALL
SELECT 1,  9 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 2,  2 UNION ALL
SELECT 2, 12 UNION ALL    
SELECT 3, 13
)
   ,  RegroupementAA (id, id_action, id_article) AS
(
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 3 UNION ALL
SELECT 3, 2, 2 UNION ALL
SELECT 4, 2, 3 UNION ALL
SELECT 5, 3, 3 UNION ALL
SELECT 6, 3, 3
)
-- Fin de vos données, vos paramètres
   ,  Liste (libelle) AS
(
SELECT 'ar3' UNION ALL
--SELECT 'ar2' UNION ALL
SELECT 'ar12'
)
   ,  Cpt_Liste (nb, nb_d) AS
(
SELECT count(*), count(DISTINCT libelle)
  FROM Liste
)
  SELECT act.id_action, act.libelle
    FROM RegroupementAA AS raa
         INNER JOIN Actions AS act
           ON act.id_action = raa.id_action
         INNER JOIN Articles AS ar1
           ON ar1.id_article = raa.id_article
         -- Bloc de proposition des articles similaires
         LEFT OUTER JOIN ArticlesSimilaires AS as1
         INNER JOIN ArticlesSimilaires AS as2
           ON as2.id_groupe = as1.id_groupe
         INNER JOIN Articles AS ar2
           ON ar2.id_article = as2.id_article
           ON as1.id_article = raa.id_article
         -- Fin proposition, jointure avec paramètres
         INNER JOIN Liste AS lst
           ON lst.libelle = coalesce(ar2.libelle, ar1.libelle)
         CROSS JOIN Cpt_Liste AS cpl
GROUP BY act.id_action, act.libelle, cpl.nb, cpl.nb_d
  HAVING count(DISTINCT raa.id_article) = count(DISTINCT lst.libelle)
     AND count(DISTINCT raa.id_article) = cpl.nb_d
     AND count(DISTINCT raa.id)         = cpl.nb
 
id_action   libelle
----------- -------
2           act2

[lead8209]

Merci beaucoup Aieuuuu voila ma requête :

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
;WITH
Liste(libellé,id) AS (--jeu d'essai => liste des artices saisis
	SELECT 'Ruban non testé au laboratoire',3 
	UNION ALL SELECT 'Fuseau',4
	UNION ALL SELECT 'Fuseau',4
	),
ListeRN AS (-- numerotation des articles
	SELECT 
		A1.id AS id_article, 
		id_groupe, 
		ROW_NUMBER() OVER (ORDER BY A1.id) AS RN
	FROM Liste
	INNER JOIN Articles A1
		ON A1.libellé = Liste.libellé
	LEFT OUTER JOIN Articles_similaires S1
		ON S1.id_article = A1.id
	),
ListeSimilaire AS (-- liste des articles "compatibles"
	SELECT 
		COALESCE(A2.id, L.id_article) AS id_article, 
		L.RN AS RN
	FROM ListeRN L
	LEFT OUTER JOIN Articles_similaires S2
		ON S2.id_groupe = L.id_groupe
	LEFT OUTER JOIN Articles A2
		ON A2.id = S2.id_article
		),
RegroupementRN AS ( --numerotation des articles pour chaque action
	SELECT 
		id_Action_Assemblage, id_article, 
		ROW_NUMBER() OVER (PARTITION BY id_Action_Assemblage ORDER BY id_article) AS RN, 
		COUNT(*) OVER (PARTITION BY id_Action_Assemblage) AS NbArticlesAction
	FROM RegroupementActionsAssemblages_A
	),
Matching AS (--matching entre la liste saisie et les actions		
	SELECT DISTINCT 
		id_Action_Assemblage,
		NbArticlesAction, 
		R.RN AS ArticleActionRN, 
		LS.RN AS ArticleListeRN
	FROM regroupementRN R
	INNER JOIN  ListeSimilaire LS
		ON R.id_article = LS.id_article
)
SELECT id_Action_Assemblage
FROM Matching
WHERE NbArticlesAction = (SELECT COUNT(*) FROM Liste) --autant d'articles pour l'action que dans la liste
GROUP BY 
	id_Action_Assemblage, 
	NbArticlesAction
HAVING 
	COUNT(DISTINCT ArticleActionRN) = NbArticlesAction --Chaque Article de l'action a une correspondance
	AND COUNT(DISTINCT ArticleListeRN) = (SELECT COUNT(*) FROM Liste) --chaque Article de la liste a une correspondance

J'ai appris pas mal de choses avec ce code Merci encore. Je pense créer le début de la requête (liste) via le code et non pas une procédure stockée.
(n'en ayant jamais fait... )

Pour Waldar quel est l'avantage de ta requête par rapport à celle de Aieuuu ?
Plus de rapidité ? plus légère ? Je vais m'y pencher dessus...

Merci encore pour vos réponses.

[aieeeuuuuu]

elle ne vérifie pas que tous les articles nécessaires à une action soient présents...
Si par exemple dans la liste on met juste art3, les actions 1 et 2 ressortent

[lead8209]

Haaa.. ok mais on obtient même chose avec une requête du type

Code :

1
2
3
4
5
 
SELECT id_Action FROM RegroupementAA WHERE id_article = 1
INTERSECT
SELECT id_Action FROM RegroupementAA WHERE id_article = 3
etc...

Comment progresser bien en SQL ? Les tutoriaux proposés sur le site permettent-ils d'acquérir le niveau pour créer des requêtes semblables ?

[aieeeuuuuu]

Nos posts se sont croisés

pas tout à fait non plus...

Elle vérifie les autres règles (pas plus d'articles saisis que d'article pour une action,...)

Comment progresser bien en SQL ? Les tutoriaux proposés sur le site permettent-ils d'acquérir le niveau pour créer des requêtes semblables ?

absolument

A commencer par cet article sur la division relationnelle... qui se rapproche de ton problème (mais difficile a mettre en place a cause des règles sur les articles "similaires" qui font qu'un article peut en cacher un autre...)

[lead8209]

Bon je vais troquer mon livre de chevet contre un bon tutoriel SQL.
Au plaisir et merci encore