Regroupement avec une contrainte

**Grinvald** · 06/05/2011, 12h27

Bonjour,

Je voudrais faire une requête sur trois tables. J'ai une table de repère squi contient un ou plusieurs articles, une table d'articles qui contient une ou plusieurs matières premières et une table de matières.

Je souhaiterai obtenir pour chaque repère les matières utilisées.

Je peux donc les obtenir facilement obtenir :

Code :

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

1
2
3
4
5
6
7
8
9
Rep1    Art1    MP1
Rep1    Art1    MP2
Rep1    Art1    MP3
Rep1    Art1    MP4
Rep1    Art2    MP1
Rep1    Art2    MP2
Rep1    Art2    MP3
Rep2    Art1    MP1
...

Mais maintenant, je voudrasi pour chaque repère, seulement les matières communes aux articles si il y en a plusieurs.

Résultat que je souhaiterais :

Code :

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

1
2
3
4
5
Rep1    MP1
Rep1    MP2
Rep1    MP3
Rep2    MP1
...

J'ai regardé avec intersect ou les group by mais je ne trouve pas mon bonheur. Je bloque vraiment et j'espère que quelqu'un pourra me venir en aide.

Merci

Grinvald

**dehorter olivier** · 06/05/2011, 13h20

bonjour

pourrais tu, au moins, indiquer la requête qui a permis d'obtenir les résultats 1
(et éventuellement la structure simplifiée des tables)

et la base de données utilisée

**Grinvald** · 06/05/2011, 14h51

J'utilise une base SQL Server et voila la structure simplifié.

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
Repere
--------------
ID_Repere
Nom
 
 
Repere_Ligne
--------------
ID_Repere
ID_Article
 
 
Article
--------------
ID_Article
Nom
 
 
Article_Ligne
--------------
ID_Article
ID_MP
 
 
MP
--------------
ID_MP
Nom

Et voila la requète utilisé :

Code :

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

1
2
3
4
5
6
SELECT Repere.Nom, Article.Nom, MP.Nom
FROM Repere
JOIN Repere_Ligne ON Repere.ID_Repere = Repere_Ligne.ID_Repere
JOIN Article ON Article.ID_Article = Repere_Ligne.ID_Article
JOIN Article_Ligne ON Article.ID_Repere = Article_Ligne.ID_Repere
JOIN MP ON MP.ID_MP = Article_Ligne.ID_MP

Désolé pour le manque d'info et merci pour ton aide.

**dehorter olivier** · 06/05/2011, 16h04

re

en utilisant "simplement ta requete" et en faisant une sous-requete

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
      SELECT Selection.Nom,
             Selection.Mp
        FROM (SELECT Repere.Nom,
                     Article.Nom AS Art,
                     Mp.Nom
                FROM Repere
                  JOIN Repere_ligne ON Repere.Id_repere = Repere_ligne.Id_repere
                  JOIN Article ON Article.Id_article = Repere_ligne.Id_article
                  JOIN Article_ligne ON Article.Id_repere = Article_ligne.Id_repere
                  JOIN Mp ON Mp.Id_mp = Article_ligne.Id_mp) Selection
      GROUP BY Selection.Nom, Selection.Mp
      HAVING Count(*) > 1

reponse rapide, qui je suis sur pourrait etre optimisee (quelque chose ne me plait pas la dedans

)

**Grinvald** · 06/05/2011, 16h23

Ta requête marche mais elle ne répond pas vraiment à mon besoin.

La requête ne garde que les valeurs en double. Ça marcherait nickel si j'avais toujours 2 articles rattachés, mais il se peut qu'il y en ait 1, 2, 3 ...
C'est tout de suite plus chaud.

**dehorter olivier** · 06/05/2011, 16h35

je ne comprends pas !

elle conserve pour tous les couples (nom, mp) pour lesquelles art se retrouve 2 fois et plus "HAVING Count(*) > 1"

ce n'est pas ce tu voulais ?

**Drizzt [Drone38]** · 06/05/2011, 17h01

Tu peux utiliser les fonctions analytiques.
A priori (cf SQL de A à Z) sur SQL Server tu as la fonction COUNT avec fenêtrage de disponible ainsi que DENSE_RANK

En gros tu dois ramener dans ta sous requete le nombre d'article ayant le même Repere et Mp (avec le COUNT fenetré)
Tu dois aussi ramener le nombre d'article par repere (avec une autre sous requete ou un max sur un DENSE_RANK)

Enfin tu as juste à ramener les lignes ou le premier nombre est égal au second si j'ai bien compris ta demande.

**Drizzt [Drone38]** · 06/05/2011, 17h02

Envoyé par dehorter olivier

je ne comprends pas !

elle conserve pour tous les couples (nom, mp) pour lesquelles art se retrouve 2 fois et plus "HAVING Count(*) > 1"

ce n'est pas ce tu voulais ?

Oui mais ça ne gère pas le cas ou il n'y a qu'un article dans le repere. Car le count(*) va renvoyer 1 dans ce cas. Et il faut le remonter car il est commun avec lui meme.

**dehorter olivier** · 06/05/2011, 17h19

salut

Mais maintenant, je voudrasi pour chaque repère, seulement les matières communes aux articles si il y en a plusieurs.

donc le "si il y en a plusieurs" fait référence aux matières et non au articles.
dans ce cas

n'ayant pas les fonctions de fenêtrage dans FIREBIRD, cela va m’être difficile de travailler dessus

et même si en bossant un peu il y aurait moyen de trouver un truc "plus classique" je laisse le fenêtrage à l'ouvrage

**Drizzt [Drone38]** · 06/05/2011, 17h30

Voici une solution Oracle avec un seul fenetrage.
J'ai simplifié les jointures entre tables pour me faire gagner du temps, mais c'est normalement pas génant pour le fond du problème.

Il te reste à retrouver la bonne syntaxe chez toi.

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
WITH 
Repere AS (SELECT 'Rep1' Nom FROM DUAL union all select 'Rep2' FROM DUAL),
Article as (select 'Rep1' rep, 'Art1' Nom from dual union all select 'Rep1' rep, 'Art2' Nom from dual
            union all select 'Rep2', 'Art5' from dual),
Mp as (select 'Art1' art, 'MP1' Nom from dual union all select 'Art1' art, 'MP2' Nom from dual
      union all select 'Art1' art, 'MP3' Nom from dual union all select 'Art1' art, 'MP4' Nom from dual
      union all select 'Art2' art, 'MP1' Nom from dual union all select 'Art2' art, 'MP2' Nom from dual
      union all select 'Art2' art, 'MP3' Nom from dual union all select 'Art5', 'MP1' from dual)
SELECT sel.Nom,
       Mp.Nom as Mp
  FROM 
  (
    SELECT Repere.Nom,
           Article.Nom AS Art,
           COUNT(1) over(partition by Repere.Nom ) AS n_art
       FROM Repere
        JOIN Article  ON Article.Rep = Repere.Nom    
  ) sel JOIN Mp ON Mp.art = sel.Art
GROUP BY sel.Nom, Mp.Nom, n_art
HAVING count(1) = n_art

**Grinvald** · 06/05/2011, 17h54

Merci bien

Je vais chercher un peu à partir de ta requête. Je vous tiens au courant.

Encore merci

**Grinvald** · 12/05/2011, 15h03

J'ai finalement utilisé SELECT CASE WHEN ...

Merci à tous et à une prochaine fois

Regroupement avec une contrainte

Langage SQL

Discussions similaires

Partager

Partager