une requête multi tables

Version imprimable

Bonjour à tous,

ci dessous, le shéma utilisé :
http://www.chronophage.com/img/Org.PNG

et là, la requête que je tente d'executer :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT          debiteurs.deb_logics            as  ReferenceLogICS,
                debiteurs.deb_nom               as  Debiteur,
                min(dossiers.doss_dateentree)   as  DateEntree,
                sum(factures.fac_montant)       as  Creance,
                sum(paiements.pai_montant)      as  Reglements,
                max(paiements.pai_date)         as  DernierReglement,
                count(paiements.pai_id)         as  NbReglements
FROM            debiteurs
LEFT JOIN       dossiers    on  debiteurs.deb_id = dossiers.deb_id
LEFT JOIN       factures    on  dossiers.doss_id = factures.doss_id
LEFT JOIN       paiements   on  dossiers.doss_id = paiements.doss_id
GROUP BY        ReferenceLogICS,
                Debiteur
;

Le résultat est faux sur le champs Creance qui est multiplié par le nbre de ligne retourné par le champs NbReglements.
J'imagine que ce sont mes jointures qui merdouillent mais je sèche ...

Merci de votre aide !

19/08/2011, 10h43
SomeoneElseIsHere

Bon la solution semble se préciser, il me faut utiliser des requêtes imbriquées, mais ca dépasse de loin mes maigres compétences en sql ... si quelqu'un peut m'aider ?!?
22/08/2011, 16h08
ced

Bonjour,

Plutôt que de nous donner la requête que tu tentes et qui ne fonctionne visiblement pas comme tu veux, si tu nous disais plutôt ce que tu souhaites obtenir ?
Un petit jeu de données et le résultat attendu nous permettraient sans doute de mieux t'aider...

au temps pour moi !
je vais essayer de décrire cela de la manière la plus simple possible.

Pour chaque débiteur, je souhaite récupérer
- la plus petite date d'entrée de l'ensemble de ses dossiers (doss_dateentree)
- la somme totale de ses factures impayées (fac_montant)
- la somme totale de ses paiements (pai_montant)
- la date de son dernier règlement (pai_date)
- le nombre de paiements effectués par ce debiteur

Table Debiteurs

Code:

1
2
3
4
5
(Deb_Id, Deb_Civilite, Deb_Prenom, Deb_Nom, Deb_DateNaissance, Deb_LieuNaissance, Deb_LogICS) VALUES
(1024, 'M', NULL, 'Dupont', NULL, NULL, '111111'), 
(1025, 'M', NULL, 'Durand', NULL, NULL, '222222'), 
(1026, 'M', NULL, 'Dupuis', NULL, NULL, '333333'), 
(1027, 'M', NULL, 'Padbol', NULL, NULL, '444444')

Table Dossiers

Code:

1
2
3
4
5
6
7
(Doss_Id, Deb_Id, Mdt_Id, Doss_DateEntree, Doss_DateCloture, Doss_MotifCloture) VALUES
(1024, 1024, 0, '01/05/2001 00:00:00', NULL, NULL), 
(1025, 1024, 0, '05/12/2009 00:00:00', NULL, NULL), 
(1026, 1024, 0, '23/08/2009 00:00:00', NULL, NULL), 
(1027, 1025, 0, '06/06/2011 00:00:00', NULL, NULL), 
(1028, 1026, 0, '09/04/2006 00:00:00', NULL, NULL), 
(1029, 1026, 0, '09/06/2004 00:00:00', NULL, NULL)

Table Factures

Code:

1
2
3
4
5
6
7
8
(Fac_Id, Doss_Id, Fac_Date, Fac_Montant, Fac_Type) VALUES
(1024, 1024, '01/05/2001 00:00:00', 568,00, NULL), 
(1025, 1025, '05/12/2009 00:00:00', 556,00, NULL), 
(1026, 1026, '23/08/2009 00:00:00', 485,00, NULL), 
(1027, 1027, '06/06/2011 00:00:00', 963,00, NULL), 
(1028, 1028, '09/04/2006 00:00:00', 487,00, NULL), 
(1029, 1029, '09/06/2004 00:00:00', 995,00, NULL), 
(1030, 1029, '01/01/2005 00:00:00', 123,00, NULL)

Table Paiements

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
(Pai_Id, Doss_Id, Pai_Montant, Pai_date, Pai_Type) VALUES
(1, 1024, 65,00, '01/01/2010 00:00:00', NULL), 
(2, 1025, 48,00, '02/02/2010 00:00:00', NULL), 
(3, 1026, 30,00, '03/03/2010 00:00:00', NULL), 
(4, 1027, 59,00, '04/04/2010 00:00:00', NULL), 
(5, 1028, 69,00, '05/05/2010 00:00:00', NULL), 
(6, 1029, 48,00, '06/06/2010 00:00:00', NULL), 
(7, 1024, 55,00, '07/07/2010 00:00:00', NULL), 
(8, 1024, 87,00, '08/08/2010 00:00:00', NULL), 
(9, 1025, 45,00, '09/09/2010 00:00:00', NULL), 
(10, 1028, 99,00, '11/11/2010 00:00:00', NULL), 
(11, 1028, 68,00, '12/12/2010 00:00:00', NULL), 
(12, 1028, 12,00, '01/01/2011 00:00:00', NULL), 
(13, 1028, -23,00, '02/02/2011 00:00:00', NULL)

Et le résultat que j'attends :

Code:

1
2
3
4
5
6
 
(ReferenceLogICS,  Debiteur, DateEntree,Creance,Reglements,DernierReglement,NbReglements)
(111111, Dupont, 2001-05-01; 1609, 330, 2010-09-09,6)
(222222, Durand, 2011-06-06, 963, 59, 2010-04-04, 1)
(333333, Dupuis, 2004-06-09, 1609, 273, 2011-02-02, 6)
(444444, Padbol, null, null, null, null, null)

OK, on y voit plus clair à présent.
Plutôt que d'utiliser des requêtes imbriquées et des sous-requêtes corrélées (ce qui va très vite dégrader les performances sous MySQL), et vu que MySQL ne dispose ni des fonctions de fenêtrage, ni des CTE, je te conseille plutôt de passer par des tables temporaires...
Le plus compliqué, dans l'histoire, ce sont les conditions du genre "la plus petite date d'entrée de l'ensemble de ses dossiers".

Voici une requête (parmi d'autres) qui permet d'obtenir cela :
Code:

1 2 3 4 5 6 7 8 SELECT db.deb_logics AS ReferenceLogICS, db.deb_nom AS Debiteur , ds1.doss_dateentree AS DateEntree FROM debiteurs AS db LEFT JOIN dossiers ds1 ON db.deb_id = ds1.deb_id LEFT JOIN dossiers ds2 ON ds1.deb_id = ds2.deb_id GROUP BY db.deb_logics, db.deb_nom, ds1.doss_dateentree HAVING COALESCE(ds1.doss_dateentree, '0000-00-00 00:00:00') = MIN(COALESCE(ds2.doss_dateentree, '0000-00-00 00:00:00')) ORDER BY db.deb_logics
Le mieux, c'est de mettre son résultat dans une table temporaire.
Tu fais la même chose pour "la date de son dernier règlement".
Enfin, tu croises ces 2 tables temporaires par jointure, et tu calcules les sommes qui restent à faire...

Désolé, mais MySQL est trop limité pour faire ça en une requête qui puisse garder ensuite des performances correctes...

Merci pour ta réponse.
Voila ce qu'on me propose par ailleurs :

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
SELECT j1.ReferenceLogICS, j1.Debiteur, j1.DateEntree, j2.Creance, j1.Reglements, j1.DernierReglement, j1.NbReglements 
from ( 
    SELECT          debiteurs.deb_logics            AS  ReferenceLogICS, 
                    debiteurs.deb_nom               AS  Debiteur, 
                    min(dossiers.doss_dateentree)   AS  DateEntree, 
                    sum(paiements.pai_montant)      AS  Reglements, 
                    max(paiements.pai_date)         AS  DernierReglement, 
                    count(paiements.pai_id)         AS  NbReglements 
    FROM            debiteurs 
    LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id 
    LEFT JOIN       paiements   ON  dossiers.doss_id = paiements.doss_id 
    GROUP BY        ReferenceLogICS, 
                    Debiteur 
    ) j1 
join (  
    SELECT          debiteurs.deb_logics            AS  ReferenceLogICS, 
                    debiteurs.deb_nom               AS  Debiteur, 
                    sum(factures.fac_montant)       AS  Creance, 
    FROM            debiteurs 
    LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id 
    LEFT JOIN       factures    ON  dossiers.doss_id = factures.doss_id 
    GROUP BY        ReferenceLogICS, 
                    Debiteur 
    ) j2 on j1.ReferenceLogICS = j2.ReferenceLogICS and j1.Debiteur = j2.Debiteur;

Cela semble fonctionner parfaitement.
Qu'en penses tu ?

23/08/2011, 12h22
ced

Oui, ça fonctionne très bien :ccool: