Forum |  HardWare.fr | News | Articles | PC | Prix | S'identifier | S'inscrire | Shop Recherche
1201 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Probleme de requete.

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

Probleme de requete.

n°1259360
Chronoklaz​m
Posté le 05-12-2005 à 17:24:05  profilanswer
 

Hello,
 
   J'ai un petit probleme avec une requete sql compatible oracle ... et meme tout sgbd respectant la norme.
 
J'ai 2 tables :
 
Ligne(numero_facture,quantité,ref_article, ligne_numero) PRIMARY KEY (numero_facture, ligne_numero)
 
Article(reference, prix) PRIMARY KEY (reference)
 
Mon but est d'obtenir la facture qui a la plus grande valeur.
 
Voici la requete qui marche au top sous Postgres
 

Code :
  1. select l.numero_facture as num from ligne l
  2. join article a on l.ref_article=a.reference
  3. GROUP BY l.numero_facture
  4. ORDER BY sum(a.pu * l.quantite)
  5. DESC LIMIT 1;


 
Mais malheuresement le LIMIT n'existe pas sous Oracle. Et donc tant qu'a faire j'ai cherché une variante un peu universelle sans LIMIT ou ROWCOUNT mais j'ai du mal.  
 
Quelqu'un aurait il une idée svp ?
 
EDIT :
 
Genre comme ca :

Code :
  1. select li.numero_facture from ligne li where li.numero_facture = max(
  2. select l.numero_facture as num from ligne l
  3. join article a on l.ref_article=a.reference
  4. GROUP BY l.numero_facture
  5. ORDER BY sum(a.pu * l.quantite)
  6. DESC));


 
mais bon ca marche pas quoi

Message cité 1 fois
Message édité par Chronoklazm le 05-12-2005 à 17:37:05

---------------
Scheme is a programmable programming language ! I heard it through the grapevine !
mood
Publicité
Posté le 05-12-2005 à 17:24:05  profilanswer
 

n°1259394
skeye
Posté le 05-12-2005 à 18:05:46  profilanswer
 

T'as essayé un truc du style :
 
select numero
from
(select numero, montant
from ...)
where montant = max(montant)
 
:??:


---------------
Can't buy what I want because it's free -
n°1259404
Chronoklaz​m
Posté le 05-12-2005 à 18:18:15  profilanswer
 

Code :
  1. select num from
  2. (select l.numero_facture as num, sum(a.pu * l.quantite) as montant from
  3. ligne_bod_rob_rep l
  4. left outer join article_bod_rob_rep a on l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite)
  7. DESC) as x
  8. where x.montant = max(x.montant);


 
Aggregates not alowed in where clause ...


---------------
Scheme is a programmable programming language ! I heard it through the grapevine !
n°1259422
skeye
Posté le 05-12-2005 à 18:45:59  profilanswer
 

ah, oui...[:petrus75]
alors avec un truc genre :
 
...
where not exists (select truc from chose2 where chose2.montant>chose1.montant)


---------------
Can't buy what I want because it's free -
n°1259485
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-12-2005 à 20:27:48  profilanswer
 

skeye > tu vas finir par y arriver :D
 

Code :
  1. select l.numero_facture as num from ligne l
  2. join article a on l.ref_article=a.reference
  3. where (a.pu * l.quantite) = (select max(a2.pu * l2.quantite
  4. from ligne l2
  5. join article a2 on l2.ref_article=a2.reference
  6. )
  7. GROUP BY l.numero_facture
  8. ORDER BY sum(a.pu * l.quantite)

n°1259487
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-12-2005 à 20:28:18  profilanswer
 

nan, spa ça non plus :sol:

n°1259492
Arjuna
Aircraft Ident.: F-MBSD
Posté le 05-12-2005 à 20:32:25  profilanswer
 

Code :
  1. create view vfacture_amount as
  2. select l.numero_facture, sum(l.quantite * a.pu) amount
  3. from ligne l inner join article a
  4. on l.ref_article = a.reference;
  5. select v1.numero_facture
  6. from vfacture_amount v1
  7. where v1.amount = (select max(v2.amount) from vfacture_amount v2);


 
PS: la vue est inutile, on peut aussi écrire comme suit (mais c'est le bordel à relire)
 

Code :
  1. select v1.numero_facture
  2. from (
  3. select l.numero_facture, sum(l.quantite * a.pu) amount
  4. from ligne l inner join article a
  5. on l.ref_article = a.reference
  6. ) v1
  7. where v1.amount = (select max(v2.amount) from (
  8. select l.numero_facture, sum(l.quantite * a.pu) amount
  9. from ligne l inner join article a
  10. on l.ref_article = a.reference
  11. ) v2);

n°1259570
skeye
Posté le 05-12-2005 à 21:47:14  profilanswer
 

Arjuna a écrit :

skeye > tu vas finir par y arriver :D


 
Flemme de tester...[:petrus75]


Message édité par skeye le 05-12-2005 à 21:47:28

---------------
Can't buy what I want because it's free -
n°1260312
Beegee
Posté le 06-12-2005 à 17:23:28  profilanswer
 

Code :
  1. SELECT num FROM (
  2. SELECT l.numero_facture as num
  3. FROM ligne l, article a
  4. WHERE l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite) DESC)
  7. WHERE rownum = 1;

n°1260317
skeye
Posté le 06-12-2005 à 17:25:49  profilanswer
 

Beegee a écrit :

Code :
  1. SELECT num FROM (
  2. SELECT l.numero_facture as num
  3. FROM ligne l, article a
  4. WHERE l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite) DESC)
  7. WHERE rownum = 1;



 

Chronoklazm a écrit :


'ai cherché une variante un peu universelle sans LIMIT ou ROWCOUNT mais j'ai du mal.  


 
Je pense que rownum n'es tpas accepté non plus...[:god]


Message édité par skeye le 06-12-2005 à 17:26:23

---------------
Can't buy what I want because it's free -
mood
Publicité
Posté le 06-12-2005 à 17:25:49  profilanswer
 

n°1260324
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 17:35:48  profilanswer
 

rownum ne marche que sur Oracle pour être plus exact, donc c'est tout aussi limitatif car non portable.
 
cherchez pas, la norme sql92 n'avait pas prévu du tout de filtrage sur les "n premiers tuples".
 
postgre est le premier à avoir ajouté cette fonction, avec "TOP" (et pour cette raison elle a été ajouté dans SQL 99, PostGre étant le SGBD le plus proche de la norme, ses propositions pour la norme sont prioritaires sur les autres produits)
ça a été repris par DB2 il me semble, access et SQL Server
 
mysql a ajouté son LIMIT
 
oracle a utilisé rownum qui ne permet pas de faire ce qu'on veut (bien plus limité que TOP et LIMIT (limit étant le plus souple))


Message édité par Arjuna le 06-12-2005 à 17:37:55
n°1260523
Beegee
Posté le 06-12-2005 à 20:24:33  profilanswer
 

Il n'est pas plus limité, il est juste plus chiant à écrire.

n°1260534
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 20:50:40  profilanswer
 

si, il est limité car il est quasi impossible de faire une requête équivalente ) "limit 5, 10".
 
avec des top imbriqués, on peut mais c'est chiant. avec le rownum, c'est la merde car il ne s'applique pas aux sous-requêtes

n°1260536
Beegee
Posté le 06-12-2005 à 20:53:51  profilanswer
 

Code :
  1. SELECT * FROM
  2. (SELECT * FROM
  3. (SELECT * FROM maTable
  4. ORDER BY monChamp)
  5. WHERE rownum <= 10)
  6. WHERE rownum >= 5;


 
C'est une habitude à prendre ... :D
 
Et puis en l'écrivant de cette façon, Oracle optimise le plan d'exécution.

n°1260625
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 22:45:27  profilanswer
 

hmmm... ça marche à partir de quelle version ?
 
parceque pour moi, rownum compte à partir de la première ligne retournée. ainsi :
 
where rownum <= x
=> Retourne X lignes, de 1 à X
 
where rownum >= x
=> Ne retroune aucune ligne, la première étant = à 1, et supprimée, alors la seconde est égale à 1 aussi et est supprimée, et ainsi de suite
 
where rownum = x
=> Retourne la première ligne si x = 1, 0 sinon. (pour la même raison que précédement)
 
C'est en tout cas le comportement que j'ai pu constater avec Oracle 7.3 et Oracle 8.0.5, confirmé par la documentation de ces deux versions.
 
Ceci dit, je n'ai jamais testé avec une version plus récente. De plus, avec ces version :
 
select * from
(select champs, ..., rownum from matable)
 
=> ca plante, "rownum" étant interdit dans une sous-requête. peut-être que ça a changé depuis, vais essayer ça dès demain au boulot :)

n°1260630
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 22:52:19  profilanswer
 

t'ain, ça marche avec la 9i chais plus combien ! :love:
 
http://magicbuzz.multimania.com/files/rownum.PNG
 
PS: Je vous rassure, chuis pas retourné au boulot en 5 minutes, y'a juste que je viens de tilter que je pouvais me connecter au taff avec Citrix Metaframe :)
 
Bien pratique de lancer TOAD depuis mon PC alors qu'il s'exécute réellement sur un serveur du taff !

n°1260632
Beegee
Posté le 06-12-2005 à 22:52:44  profilanswer
 

Ca m'apprendra à taper trop vite, en fait il faut aliaser le rownum dans la sous-requête pour que ça marche :)
 
SELECT * FROM  
(SELECT rownum as rnum, sub.* FROM  
(SELECT * FROM maTable  
ORDER BY monChamp) sub
WHERE rownum <= 10)
WHERE rnum >= 5;

n°1260633
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 22:53:21  profilanswer
 

En regardant les codes produit de la base de prod au boulot, je me demande pourquoi j'ai pas fait les tests sur la base de dev, elle à l'air plus propre :lol:

n°1260634
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 22:54:01  profilanswer
 

Ceci dit, sûr et certain, jusqu'à la version 8.0.5, ça ne marchait pas !
 
Tiens, vais faire un test d'ailleurs, j'ai un doute affreux...

n°1260636
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 23:01:22  profilanswer
 

[MagicBuzz qui découvre oracle, sur lequel il travaille depuis 6 ans]
 
select rownum, codpro
from (select codpro from pro
where codsoc = 2 order by codpro)
where rownum <= 5
 
t'ain, c'est tout le fonctionnement de rownum qui a changé en fait !
avant, un test sur le rownum s'executait comme un having, c'est à dire une fois la requête terminée et les tri effectués
 
et maintenant, on peut utiliser order by dans une sous-requête !
 
[/MagicBuzz qui découvre oracle, sur lequel il travaille depuis 6 ans]

n°1260637
Arjuna
Aircraft Ident.: F-MBSD
Posté le 06-12-2005 à 23:02:42  profilanswer
 

en fait, maintenant, c'est limite plus souple que TOP n, mise à part qu'on doit forcément passer par une sous-requête si on veut que ça respecte le tri

n°1260712
skeye
Posté le 07-12-2005 à 07:27:52  profilanswer
 

Ah mais alors ça c'est bon à savoir! [:huit]


Message édité par skeye le 07-12-2005 à 07:28:00

---------------
Can't buy what I want because it's free -
n°1260745
Beegee
Posté le 07-12-2005 à 09:51:00  profilanswer
 

Code :
  1. SELECT * FROM 
  2. (SELECT ROWNUM AS rnum, sub.* FROM 
  3. (SELECT * FROM CUSTOMER 
  4. ORDER BY customer_ref) sub
  5. WHERE ROWNUM <= 10)
  6. WHERE rnum >= 5;


 
Plan d'exécution avec ce type de requête Oracle :
 
SELECT STATEMENT Hint=RULE  
  VIEW
    COUNT STOPKEY
      VIEW
        TABLE ACCESS BY INDEX ROWID      CUSTOMER
          INDEX FULL SCAN                       CUSTOMER_PK
 
On voit bien le COUNT STOPKEY qui signifie que l'index (ici le champ est indexé) est parcouru, mais pas intégralement (on s'arrête à la 10ème valeur).
 
Evidemment, il ne faut pas inverser la requête et faire rownum >= 5 en 1er, car alors rien n'est renvoyé (étant donné que le rownum ne s'incrémente que lorsqu'une ligne est renvoyée ...).

mood
Publicité
Posté le   profilanswer
 


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Probleme de requete.

 

Sujets relatifs
[résolu] Requete MySQL : problème d'affichage par catégorie[ACCESS] probleme cumul sur requete
Problème Requete / TimestampProbléme avec une requete
[SQL*PLUS] Probleme avec caractere special & dans requete SQLprobleme de requete
Probleme de requete et de texte en php [Résolu, euh en fait non]Probleme de requete
problème de requête[VBA] Access, probleme de requete
Plus de sujets relatifs à : Probleme de requete.


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR