IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Regrouper 3 lignes en provenance de 2 tables


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Mars 2008
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2008
    Messages : 6
    Par défaut Regrouper 3 lignes en provenance de 2 tables
    Bonjour.

    J'ai une table CONTRATS qui a les champs ID_CONTRAT, ID_GARANTIE et TAUX (de la garantie). Cette table contient une ligne par contrat.

    J'ai aussi une table CONTRAT_GARANTIE qui a les champs ID_CONTRAT, ID_GARANTIE et MONTANT. Cette table peut contenir une ou deux garanties (donc une ou deux lignes) pour chaque contrat.

    Je dois créer une requête qui, pour chaque contrat, me sort sur une même ligne l'Id du contrat et les infos de la ou des garanties de ce contrat.
    Autrement dit, on doit se retrouver avec une ligne qui comporte les champs suivants : ID_CONTRAT, ID_GARANTIE_1, MONTANT_GARANTIE_1, TAUX_GARANTIE_1, ID_GARANTIE_2, MONTANT_GARANTIE_2, TAUX_GARANTIE_2.

    Sachant que
    - quand une garantie est présente dans CONTRAT_GARANTIE mais pas dans CONTRATS, son champ TAUXGARANTIE est affecté à zéro
    - quand un contrat n'a qu'une seule garantie dans CONTRAT_GARANTIE, les champs de la deuxième garantie sont affectés à NULL


    EXEMPLE :
    Table CONTRATS :
    Contrat1 --- Garantie1 --- Taux1
    Contrat2 --- Garantie2 --- Taux2

    Table CONTRAT_GARANTIE :
    Contrat1 --- Garantie1 --- Montant1
    Contrat2 --- Garantie2 --- Montant2
    Contrat2 --- Garantie3 --- Montant3

    Requête :
    Contrat1 --- Garantie1 --- Montant1 --- Taux1 --- NULL --- NULL --- NULL
    Contrat2 --- Garantie2 --- Montant2 --- Taux2 --- Garantie3 --- Montant3 --- 0



    J'ai réussi à le faire grâce au code suivant mais il est beaucoup trop gourmand en ressources et en temps donc j'aimerais savoir s'il existe un moyen de faire la même chose mais beaucoup plus rapidement.
    N.B. Le "ELSE NULL" est sous-entendu dans les CASE.


    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
    29
     
    SELECT CNT_GAR.IDCONTRAT                    IDCONTRAT,
           GAR1                                 IDGARANTIE1,
           CONTRAT1.TAUX                        TAUX_1,
           GARANTIE1.MONTANT                    MONTANT_1,
           CASE WHEN GAR1 <> GAR2 THEN GAR2 END IDGARANTIE_2,
           CASE WHEN GAR1 <> GAR2 THEN 0    END TAUX_2,
           GARANTIE2.MONTANT                    MONTANT_2,
    FROM
         (
           SELECT IDCONTRAT,
                  MIN(IDGARANTIE) GAR1,
                  MAX(IDGARANTIE) GAR2
           FROM CONTRAT_GARANTIE
           GROUP BY IDCONTRAT
         ) CNT_GAR
    LEFT JOIN CONTRATS CONTRAT1
           ON (     CONTRAT1.IDCONTRAT  = CNT_GAR.IDCONTRAT
                AND CONTRAT1.IDGARANTIE = CNT_GAR.GAR1
              )
    LEFT JOIN CONTRAT_GARANTIE GARANTIE1
           ON (     GARANTIE1.IDCONTRAT  = CNT_GAR.IDCONTRAT
                AND GARANTIE1.IDGARANTIE = CNT_GAR.GAR1
              )
    LEFT JOIN CONTRAT_GARANTIE GARANTIE2
           ON (     GARANTIE2.IDCONTRAT  = CNT_GAR.IDCONTRAT
                AND GARANTIE2.IDGARANTIE = CNT_GAR.GAR2
              )
    ;

  2. #2
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    quelle version de base ?

  3. #3
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Mars 2008
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2008
    Messages : 6
    Par défaut
    9i

    Mais c'est bon : j'ai pu résoudre mon problème en externalisant les jointures dans la requête appelante (celle que j'ai postée est en fait une sous-requête de cette requête appelante).

  4. #4
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    Avec les fonctions analytiques, un seul accès aux tables.

  5. #5
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Mars 2008
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2008
    Messages : 6
    Par défaut
    J'avais essayé la solution suivante mais ça dégradait encore plus les performances.

    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
    29
    30
    31
     
    SELECT LISTE_CONTRATS.IDCONTRAT             IDCONTRAT,
           GARANTIE1.IDGARANTIE                 IDGARANTIE1,
           GARANTIE1.MONTANT                    MONTANT_1,
           GARANTIE1.TAUX                       TAUX_1,
           GARANTIE1.IDGARANTIE                 IDGARANTIE1,
           GARANTIE1.MONTANT                    MONTANT_1,
           CASE WHEN (GARANTIE2.GARANTIE IS NOT NULL) THEN 0 END TAUX_2
    FROM ( SELECT DISTINCT IDCONTRAT
           FROM CONTRATS
         ) LISTE_CONTRATS
    LEFT JOIN
              ( SELECT CG.IDCONTRAT, CG.IDGARANTIE, MONTANT, TAUX,
                       ROW_NUMBER() OVER (PARTITION BY CG.IDCONTRAT ORDER BY CG.IDCONTRAT) NUMERO_GARANTIE
                FROM CONTRAT_GARANTIE CG
                LEFT JOIN CONTRATS CONTRAT1
                       ON (     CONTRAT1.IDCONTRAT  = CG.IDCONTRAT
                            AND CONTRAT1.IDGARANTIE = CG.IDGARANTIE
                          )
              ) GARANTIE1
           ON (     GARANTIE1.IDCONTRAT = LISTE_CONTRATS.IDCONTRAT
                AND GARANTIE1.NUMERO_GARANTIE = 1
              )
    LEFT JOIN
              ( SELECT CG.IDCONTRAT, CG.IDGARANTIE, MONTANT,
                       ROW_NUMBER() OVER (PARTITION BY CG.IDCONTRAT ORDER BY CG.IDCONTRAT) NUMERO_GARANTIE
                FROM CONTRAT_GARANTIE CG
              ) GARANTIE2
           ON (     GARANTIE2.IDCONTRAT = LISTE_CONTRATS.IDCONTRAT
                AND GARANTIE2.NUMERO_GARANTIE = 2
              )

  6. #6
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    Avec les fonctions analytiques
    (LEAD : On regarde les données de la ligne suivante si elles appartiennent au même contrat, ROW_NUMBER() : on récupère le n° de ligne par contrat )
    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
    WITH contrat AS (
                 SELECT 1 AS nocont, 'gar1' AS gar, 'taux1' AS taux FROM dual
        UNION ALL SELECT 2 AS nocont, 'gar2' AS gar, 'taux2' AS taux FROM dual),
    contrat_garantie AS (
                SELECT 1 AS nocont, 'gar1' AS gar, 'mt1' AS mont FROM dual
        UNION ALL SELECT 2 AS nocont, 'gar2' AS gar, 'mt2' AS mont FROM dual
        UNION ALL SELECT 2 AS nocont, 'gar3' AS gar, 'mt3' AS mont FROM dual)
    SELECT nocont, gar, mont, taux, garantie2, montant2,taux2
    FROM (
        SELECT row_number() OVER(PARTITION BY c.nocont ORDER BY g.gar) AS num,
                c.nocont, c.gar, g.mont, c.taux, 
                LEAD(g.gar, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS garantie2,
                LEAD(g.mont, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS montant2,
                LEAD(0, 1) OVER (PARTITION BY c.nocont ORDER BY g.gar) AS taux2
        FROM contrat c, contrat_garantie g
        WHERE c.nocont = g.nocont
        ORDER BY c.nocont, g.gar        
        )
    WHERE num = 1
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    NOCONT	GAR	MONT	TAUX	GARANTIE2	MONTANT2	TAUX2
    1	gar1	mt1	taux1			
    2	gar2	mt2	taux2	gar3	mt3	0

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. table regroupant les lignes en doubles
    Par jamelie dans le forum SAS Base
    Réponses: 7
    Dernier message: 10/04/2008, 14h46
  2. Réponses: 5
    Dernier message: 03/02/2006, 10h45
  3. [vbexcel]tri ou regroupement de ligne en fonction de valeur.
    Par Mugette dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 12/12/2005, 18h22
  4. [MySQL] Liste déroulante provenant d'une table
    Par richton95 dans le forum PHP & Base de données
    Réponses: 3
    Dernier message: 29/11/2005, 10h07
  5. selectionner une ligne au hasard dans une table
    Par dark_vidor dans le forum Requêtes
    Réponses: 2
    Dernier message: 27/06/2005, 12h01

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo