Problème sur une requête SQL (PL/SQL)

**goofyrocks** · 13/01/2009, 15h55

Bonjour,

Je suis entrain de faire un projet de pl/sql. J'ai un petit souci d'optimisation d'une procédure.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
CREATE TABLE tclient (
  idclient int PRIMARY KEY,
  nom varchar(100),
  prenom varchar(100),
  adresse  varchar(600),
  cp varchar(10),
  vil varchar(100),
  tel varchar(10)
);
 
-- --------------------------------------------------------
--
-- Structure de la table tvehicule
--
 
CREATE TABLE tvehicule (
  immat varchar(25) PRIMARY KEY,
  type varchar(25) ,
  marque varchar(100),
  modele varchar(100),
  idclient int,
  FOREIGN KEY (idclient) REFERENCES tclient(idclient),
  CONSTRAINT invalid_type_vehicule CHECK( type in('tvoiture','tmoto'))
) ;
 
-- --------------------------------------------------------
--
-- Structure de la table tvoiture
--
 
CREATE TABLE tvoiture (
  immat varchar(25) PRIMARY KEY, 
  carburant varchar(100) ,
  nbporte int, 
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
-- --------------------------------------------------------
--
-- Structure de la table tmoto
--
 
CREATE TABLE tmoto (
  immat varchar(25) PRIMARY KEY,
  cylindre int ,
  categorie varchar(100),
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
 
 
 
---------------------------------------------
--
-- Structure de la table tfacture
--
CREATE TABLE tfacture (
  idfacture int PRIMARY KEY,
  fdate date,
  paiement real,
  km int,
  immat varchar(25),
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
--------------------------------------------------------------
--
-- Structure de la table tfligne
--
 
CREATE TABLE tfligne (
  idligne int PRIMARY KEY,
  txt varchar(255),
  qte real ,
  prixunit real,
  tva real ,
  idfacture int,
  FOREIGN KEY (idfacture) REFERENCES  tfacture(idfacture)
 
) ;
 
INSERT INTO tclient VALUES (0,'MOSBY','Ted','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (1,'ALDRIN','Lily','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (2,'ERIKSEN','Marshall','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (3,'STINSON','Barney','2165 Looghe Road','X54RT','NEW YORK','0988767321');
INSERT INTO tclient VALUES (4,'SCHERBATSKY','Robin','12 Canada Street','X54RT','NEW YORK','0977553433');
 
 
INSERT INTO tvehicule VALUES('2344TF54','tvoiture','RENAULT','R5',1);
INSERT INTO tvehicule VALUES('430TD79','tvoiture','BMW','X3',3);
INSERT INTO tvehicule VALUES('3024SZ79','tmoto','BMW','K1200R',3);
INSERT INTO tvehicule VALUES('1730SG79','tvoiture','BMW','X2',2);
INSERT INTO tvehicule VALUES('8133ST79','tmoto','SUZIKI','GSXR',4);
INSERT INTO tvehicule VALUES('7290TL75','tvoiture','PEUGEOT','1004',0);
 
 
INSERT INTO tfacture VALUES(0,sysdate,50,56679,'1730SG79');
INSERT INTO tfligne VALUES(0,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(1,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(2,'Disque',1,25,19.6,0);
INSERT INTO tfligne VALUES(3,'Divers',2,12,19.6,0);
 
INSERT INTO tfacture VALUES(1,sysdate,99.268,78679,'8133ST79');
INSERT INTO tfligne VALUES(4,'Main d oeuvre',1,30,19.6,1);
INSERT INTO tfligne VALUES(5,'Huile',2,12,19.6,1);
INSERT INTO tfligne VALUES(6,'Filtre',1,5,19.6,1);
INSERT INTO tfligne VALUES(7,'Divers',2,12,19.6,1);
 
INSERT INTO tfacture VALUES(2,sysdate,0,123679,'7290TL75');
INSERT INTO tfligne VALUES(8,'Main d oeuvre',1,30,19.6,2);
INSERT INTO tfligne VALUES(9,'Pneu',4,124,19.6,2);
 
INSERT INTO tfacture VALUES(3,sysdate,50,70679,'1730SG79');
INSERT INTO tfligne VALUES(10,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(11,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(12,'Divers',2,12,19.6,0);
 
INSERT INTO tfacture VALUES(4,sysdate,50,56679,'1730SG79');
INSERT INTO tfligne VALUES(13,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(14,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(15,'Disque',1,25,19.6,0);
INSERT INTO tfligne VALUES(16,'Divers',2,12,19.6,0);

je travaille selon ce modéle:
Tclient(Idclient,nom, prenom,adresse..)
Tvehicule(Immat,marque, modèle,...,#idclient)
Tfacture(idfacture,date,paiement,#immat)
Tfligne(idligne,txt,prixunit,qte,tva,#idfacture)

Tfacture correspond à l'entete de la facture
Tfligne correspond à une ligne de la facture
paiement correspond au prix payé par le client ( dans l'idée qui puisse payer en plusieurs fois)

Le but de ma procédure doit me retourner les mauvais payeurs avec le montant de leurs dettes.
Je souhaiterais le faire en une seule requête

Code :

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

1
2
3
4
5
SELECT cli.idclient,SUM(prixunit*qte+(prixunit*qte*tva)/100)- SUM(fac.paiement) AS MONTANT
FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig
WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND fac.idfacture=lig.idfacture
GROUP BY cli.idclient
ORDER BY MONTANT DESC;

Le souci vient des jointures pour une seule facture,SUM(fac.paiement) me compte plusieurs fois le montant paiement, à vérifier mais serment le même nombre de fois qu'il y a de ligne.

Je visualise à peu prêt le problème mais ne trouve pas de solution sans faire 36 milles requête.

Merci

Goofy

**hazamor** · 13/01/2009, 16h16

Envoyé par goofyrocks

Code :

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

...SUM(fac.paiement) AS MONTANT

pourquoi tu utilise 'sum(fac.paiment)', c'est faux.

La requete est la suivante:

Code :

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

1
2
3
4
5
SELECT cli.idclient,(SUM(prixunit*qte+(prixunit*qte*tva)/100)- fac.paiement) AS MONTANT
FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig
WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND fac.idfacture=lig.idfacture
GROUP BY cli.idclient
ORDER BY MONTANT DESC;

**SQLpro** · 13/01/2009, 16h21

Merci de respecter la charte de postage de ce forum en publiant le DDL de vos table...
http://www.developpez.net/forums/a69...gage-sql-lire/

A +

**hazamor** · 13/01/2009, 16h22

OU comme ça:

Code :

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

1
2
3
4
5
SELECT cli.idclient, (cal.payer- fac.paiement) AS MONTANT
from client, (select cli.idclient , SUM(prixunit*qte+(prixunit*qte*tva)/100)
                FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig
              WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND          fac.idfacture=lig.idfacture
              GROUP BY cli.idclient) as cal;

**Waldar** · 13/01/2009, 16h41

Envoyé par hazamor

pourquoi tu utilise 'sum(fac.paiment)', c'est faux.

Pourquoi un client ne pourrait-il pas avoir plusieurs factures (s'il a plusieurs voitures par exemple) ?

Il faut d'abord regrouper l'état des paiments par facture, puis par voiture, et enfin par client.

**goofyrocks** · 13/01/2009, 17h33

Envoyé par Waldar

Pourquoi un client ne pourrait-il pas avoir plusieurs factures (s'il a plusieurs voitures par exemple) ?

Il faut d'abord regrouper l'état des paiments par facture, puis par voiture, et enfin par client.

Exacte c'est pour cette raison.
J'ai rajouté le DDL dans le premier poste. Désolé de ne pas l'avoir mis plutôt.

Problème sur une requête SQL (PL/SQL)

Langage SQL

Discussions similaires

Partager

Partager