Comment mettre en colonne des resultats de plusieurs lignes

Comment mettre en colonne des resultats de plusieurs lignes - SQL/NoSQL - Programmation

Marsh Posté le 06-02-2006 à 11:37:17    

Bonjour,
 
Je viens soliciter votre aide pour un probleme de requete. Non sur le code exact, mais plutot sur la methode à utiliser.
 
Voila imaginons que j'ai deux tables comme celles-ci:
 
BoiteDeVitesse                                   rejet
--------------                                --------
id_boite                                          id_rejet
id_rapport                                       id_boite
test_date                                       rej
test_heure
num_banc
 
Donc la requete que je veux c'est je veux la liste de toutes les boites de vitesses et de leur 5 premiers rejets. (si il n'y en a moins de 5 on affiches rien).
Comme ceci
id_boite  |  id_rapport  |  test_date  |  test_heure  |  num_banc  |  rej1  |  rej2  |  rej3  |  rej4 | rej5
 
Le probleme est que si je mets un select dans un autre select je n'arrive à lister que si il n'y a qu'un seul rejet associé à la boite.
 
select B.id_boite, B.id_rapport, B.test_date, B.test_heure, B.num_banc, B.rej1, B.rej2, B.rej3, B.rej4, B.rej5
(SELECT rej FROM reject)  
From BoiteDeVitesse B


Message édité par creusois le 06-02-2006 à 13:16:12
Reply

Marsh Posté le 06-02-2006 à 11:37:17   

Reply

Marsh Posté le 06-02-2006 à 13:07:19    

Pour commencer, qu'est-ce qu'un rejet ? (un peu de culture g ;)).
 
Est-il important d'ordonner les rejets ? Ou bien l'ordre de lecture n'a pas d'importance.
 
Connaît-tu le nombre de rejets maximum à l'heure actuelle ? As-tu une idée de comment peux évoluer ce maximum dans le futur ?


Message édité par jeoff le 06-02-2006 à 13:08:33
Reply

Marsh Posté le 06-02-2006 à 13:21:46    

Merci de ta reponse,
 
Un rejet c'est un numero de boite qui ne passe pas au test.
 
Donc oui il y a un sens pour l'ordre de lecture. J'ai rajouté en haut dans mes tables un id_rejet, il faudrait que liste seulement les 5 premiers rejets (donc les 5 premiers id_rejet qui ont le meme id_boite).
 
Pour ce qui est des limites, j'ai conventionné la taille d'un int comme valeur maximale.
 
Merci pour ton aide

Reply

Marsh Posté le 06-02-2006 à 13:43:41    

Ok merci pour l'éclaircissement sur le sens de rejet (je pensais que c'était un terme plus technique).
 
Par contre je n'ai pas du m'exprimer correctement donc je vais reformuler.
 
Ton id_boîte représente-t-il un numéro de lot ? Une boîte spécifique ?
 
Si il représente un numéro de lot, l'ordre de lecture/affichage des infos, doit-il être obligatoirement l'ordre dans lequel tes boîtes ont été inserées en BDD.
 
Autrement dit, celà pose-t-il un problème si on affiche rejet 3 puis rejet 1 puis rejet 2 ... etc
 
La base existe déjà ?  
As-tu un "énoncé" qui définit le fonctionnement de ce que tu souhaite modéliser ?  
Un cahier des charges est-il écrit ?
 
Il est peut être utile de reprendre ton explication de 0. Commence par nous décrire le fonctionnement de ton système.
Puis identifie les données que tu dois stocker. Ce dernier point ne me semble pas clair.

Message cité 1 fois
Message édité par jeoff le 06-02-2006 à 13:48:07
Reply

Marsh Posté le 06-02-2006 à 14:13:43    

jeoff a écrit :


Ton id_boîte représente-t-il un numéro de lot ? Une boîte spécifique ?


Il s'agit d'un numero commun. c'est un identifiant sql (autoincrementé) il s'agit aussi de la clef primaire de la premiere table (boitedevitesse)
 
Pour le reste j'ai un peu du mal à te comprendre. Clairement je dois utiliser un logiciel specifique, qui m'oblige à faire tout ceci en une seule et meme requete (sinon j'aurais fais une premiere requete sur la table boite de vitesse, recuperer l'id_boite et faire une deuxieme requete avec TOP 5 et dans le WHERE l'id_boite.
Mon probleme est donc que dans une requete je dois sortir la fiche sur la boite de vitesse, plus une "vision d'ensemble" sur les rejets potentiels de cette boite (donc 5 rejets de cette boite). Pour le moment je peux mettre de coté l'ordre de sortie des rejets. Par contre il faut que une boite et ses 5 rejets soient sur la meme ligne.
 
Par la suite l'utilisateur voit cette vision d'ensemble (il a une vu d'ensemble sur les boites sorties durant la nuit/jour et des rejets potentiels.). Et si il juge necessaire, il pourra avoir un rapport complet d'une boite avec tous les rejets de cette derniere.  
 
J'espere avoir été clair.
Merci bien.

Reply

Marsh Posté le 06-02-2006 à 17:17:33    

Pour avoir 5 colonnes rejets il faut que tu mettes 5 fois la table rejets en jointure... après faut que tu filtres pour n'avoir que les bons... la commence la galère...
 
un truc du genre pour commencer
 
select A.id_boite, A.id_rapport, A.test_date, A.test_heure, A.num_banc, B.rej, C.rej, D.rej, E.rej, F.rej
From BoiteDeVitesse A, rejet B, rejet C, rejet D, rejet E, rejet F
where A.id_boite = B.id_boite
and A.id_boite = C.id_boite
and A.id_boite = D.id_boite
and A.id_boite = E.id_boite
and A.id_boite = F.id_boite
 
reste à filtrer ensuite parce que tu vas récupérer beaucoup trop de lignes...
 

Reply

Marsh Posté le 06-02-2006 à 17:20:41    

pour les filtres faudrait un "where not exists a.id_rejet = b.id_rejet etc."
 
Je te conseille le test sur une toute petite table...

Reply

Marsh Posté le 06-02-2006 à 17:41:13    

edit je crois que la solution t'es donnée au dessus, perso je sèche :d


Message édité par jeoff le 06-02-2006 à 17:42:51
Reply

Marsh Posté le 06-02-2006 à 22:11:41    

Quel SGBD ?
 
Si Oracle :
 

Code :
  1. select bdv.id_boite, bdv.id_rapport, bdv.test_date, bdv.test_heure, bdv.num_banc, sub.rej1, sub.rej2, sub.rej3, sub.rej4, sub.rej5
  2. from BoiteDeVitesse bdv,
  3.      (select id_boite,
  4.             max(decode(rn,1,r.rej)) as rej1,
  5.             max(decode(rn,2,r.rej)) as rej2,
  6.             max(decode(rn,3,r.rej)) as rej3,
  7.             max(decode(rn,4,r.rej)) as rej4,
  8.             max(decode(rn,5,r.rej)) as rej5,
  9.      from (select id_boite, rej, row_number() over (partition by id_boite order by id_rejet) as rn from rejet)
  10.      group by id_boite) sub
  11. where bdv.id_boite = sub.id_boite;


 
Fully untested, of course :D

Reply

Marsh Posté le 07-02-2006 à 08:13:35    

Pardon oui j'avais oublié de preciser que je suis sous SQL Server donc Beegee merci mais...
 
Pour dlaumor je suis bien arrivé tt seul à faire la requete que tu m'as donné, mais justement le probleme c'est le filtre apres. Je n'arrive pas a eliminer les mauvaises reponses.

Message cité 1 fois
Reply

Marsh Posté le 07-02-2006 à 08:13:35   

Reply

Marsh Posté le 07-02-2006 à 10:19:33    

Pour ceux que ça intéresse, voici la solution Oracle :
 

Code :
  1. DROP TABLE BoiteDeVitesse;
  3. DROP TABLE rejet;
  5. CREATE TABLE BoiteDeVitesse
  6. (id_boite NUMBER(9),
  7. id_rapport NUMBER(9),
  8. test_date VARCHAR2(8),
  9. test_heure VARCHAR2(6),
  10. num_banc NUMBER(9));
  12. CREATE TABLE rejet
  13. (id_rejet NUMBER(9),
  14. id_boite NUMBER(9),
  15. rej VARCHAR2(100));
  17. INSERT INTO BoiteDeVitesse
  18. VALUES (1, 1, '20050101', '120000', 1);
  20. INSERT INTO BoiteDeVitesse
  21. VALUES (2, 2, '20050102', '120100', 2);
  23. INSERT INTO BoiteDeVitesse
  24. VALUES (3, 3, '20050103', '120200', 3);
  26. INSERT INTO rejet
  27. VALUES (1, 1, 'rejet 1 boîte 1');
  29. INSERT INTO rejet
  30. VALUES (2, 1, 'rejet 2 boîte 1');
  32. INSERT INTO rejet
  33. VALUES (3, 1, 'rejet 3 boîte 1');
  35. INSERT INTO rejet
  36. VALUES (4, 1, 'rejet 4 boîte 1');
  38. INSERT INTO rejet
  39. VALUES (5, 1, 'rejet 5 boîte 1');
  41. INSERT INTO rejet
  42. VALUES (6, 2, 'rejet 1 boîte 2');
  44. INSERT INTO rejet
  45. VALUES (7, 2, 'rejet 2 boîte 2');
  47. SELECT bdv.id_boite, bdv.id_rapport, bdv.test_date, bdv.test_heure, bdv.num_banc, sub.rej1, sub.rej2, sub.rej3, sub.rej4, sub.rej5
  48. FROM BoiteDeVitesse bdv,
  49.      (SELECT id_boite,
  50.              MAX(DECODE(rn,1,rej)) AS rej1,
  51.              MAX(DECODE(rn,2,rej)) AS rej2,
  52.              MAX(DECODE(rn,3,rej)) AS rej3,
  53.              MAX(DECODE(rn,4,rej)) AS rej4,
  54.              MAX(DECODE(rn,5,rej)) AS rej5
  55.      FROM (SELECT id_boite, rej, row_number() OVER (PARTITION BY id_boite ORDER BY id_rejet) AS rn FROM rejet)
  56.  GROUP BY id_boite) sub
  57. WHERE bdv.id_boite = sub.id_boite;


 
et le résultat :
 
ID_BOITE,ID_RAPPORT,TEST_DATE,TEST_HEURE,NUM_BANC,REJ1,REJ2,REJ3,REJ4,REJ5
1,1,20050101,120000,1,rejet 1 boîte 1,rejet 2 boîte 1,rejet 3 boîte 1,rejet 4 boîte 1,rejet 5 boîte 1
2,2,20050102,120100,2,rejet 1 boîte 2,rejet 2 boîte 2,,,


Message édité par Beegee le 07-02-2006 à 10:20:45
Reply

Marsh Posté le 07-02-2006 à 10:20:01    

creusois a écrit :

Pardon oui j'avais oublié de preciser que je suis sous SQL Server donc Beegee merci mais...
 
Pour dlaumor je suis bien arrivé tt seul à faire la requete que tu m'as donné, mais justement le probleme c'est le filtre apres. Je n'arrive pas a eliminer les mauvaises reponses.


 
 
Il faut que tu élimines les lignes où les colonnes sont identiques
T'as essayé d'ajouter les conditions du genre  


where ...
and B.id_rejet <> C.id_rejet
and B.id_rejet <> D.id_rejet
and B.id_rejet <> E.id_rejet
and B.id_rejet <> F.id_rejet
and C.id_rejet <> D.id_rejet
and C.id_rejet <> E.id_rejet
and C.id_rejet <> F.id_rejet
and D.id_rejet <> E.id_rejet
and D.id_rejet <> F.id_rejet
and E.id_rejet <> F.id_rejet

Reply

Marsh Posté le 07-02-2006 à 11:17:28    

dlaumor ca filtre bien, mais ce n'est pas encore suffisant. Il y a encore pas mal de doublon
 
rej1 = 105 rej2 = 200
et la ligne du dessous  
rej1 = 200 rej2 = 105
...

Message cité 1 fois
Reply

Marsh Posté le 07-02-2006 à 11:29:10    

Quelle version de SQL SERVER ?
2005 ?
 
Ils ont introduit des mots-clé comme PIVOT / UNPIVOT qui doivent permettre de trouver une solution simple à ce genre de problème.

Reply

Marsh Posté le 07-02-2006 à 12:04:06    

Peut-être un début de solution...
 
On part du principe que le premier rejet à l'id le plus petit, le deuxième -> le deuxième plus petit etc...
 
(requête faite pour les 3 premiers rejets pour aller plus vite, il manque aussi la jointure avec la table BoiteDeVitesse mais la difficulté n'est pas là ;) )
 

Code :
  1. select R1.id_boite, R1.rej as rej1, R2.Rej as rej2, R3.Rej as rej3
  2. from rejet R1, rejet R2,  rejet R3
  3. where R1.id_rejet = (select min(id_rejet) from rejet where id_boite = R1.id_boite)
  4. and R2.id_rejet = (select top 1 id_rejet from rejet where id_rejet not in (R1.id_rejet) and id_boite = R1.id_boite)
  5. and R3.id_rejet = (select top 1 id_rejet from rejet where id_rejet not in (R1.id_rejet,R2.id_rejet) and id_boite = R1.id_boite)


 
Problème : Ca ne retourne pas de ligne s'il y a moins de 3 rejets

Reply

Marsh Posté le 07-02-2006 à 12:05:26    

creusois a écrit :

dlaumor ca filtre bien, mais ce n'est pas encore suffisant. Il y a encore pas mal de doublon
 
rej1 = 105 rej2 = 200
et la ligne du dessous  
rej1 = 200 rej2 = 105
...


 
 
Essai en ajoutant des clauses genre supérieur inférieur


...
and B.id_rejet < C.id_rejet
and c.id_rejet < D.id_rejet
...


 
Voila comme dit Dionysos33, grillage en beauté :)


Message édité par dlaumor le 07-02-2006 à 12:06:18
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed