Discussion :

[mihaispr]

Bonjour a tous!

J'ai 3 tableaux voila le DDL:

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
 
CREATE DATABASE ParcAuto
 
CREATE TABLE PatrimoniuMasini
(
IdMasina int PRIMARY KEY NOT NULL IDENTITY(1,1),
NumarMasina varchar(255) UNIQUE,
PersoanaFolMasina varchar(255),
Utilizator varchar(255)
)
 
 
-- utilizator e firma careia ii apartine automobilul sau l-a inchiriat
 
-- tabela2 TipuriAutomobile
 
CREATE TABLE TipuriAutomobile
(IdTip int PRIMARY KEY NOT NULL,
IdMasina int,
TipAutomobil varchar(255),
CapacitateMotor varchar(255),
CapacitateTransport varchar(255),
TipCombustibil varchar(255),
NormaL100KM decimal(3,2) -- 3 zecimale in total , 2 zecimale dupa virgula
)
 
 
 
-- un numar de masina poate face mai multe drumuri
 
CREATE TABLE FoaieParcursMasina
(IdFoaie int PRIMARY KEY NOT NULL,
DataFoaieParcurs datetime,
IdMasina int,
NumarMasina varchar(255),
DataOraPlecare datetime,
DataOraSosire datetime,
NumeSofer varchar(255),
LocalitatePlecare varchar(255),
LocalitateSosire varchar(255),
KM int,
CombustibilConsumat int, --combustibil consumat =km*normaL100km
MotivDeplasare varchar(255),
MarfaTransportata varchar(255),
Utilizator varchar(255)
)

Les lignes sont inserees avec INSERT INTO.

Consigne requete:

Je veux lister la moyenne du combustible(AVG(CombustibilConsumat) ) consomme d'une periode(la periode est represente par 2 colonnes DataOraSosire et DataOraPlecare) groupes par types d'automobiles (c'est IdTip) et type du combustible (colonne TipCombustibil)

Je veux la moyenne du combustibleconsomme donc AVG sur la colonne nomme CombustibilConsumat .(GROUP BY IdTip et TipCombustibil).


J'ai essaye comme ca:

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
 
SELECT tab1.DataOraPlecare,tab1.DataOraSosire,tab2.IdTip,tab2.TipCombustibil,
x.MoyenneCombustible
FROM FoaieParcursMasina tab1
GROUP BY tab1.DataOraPlecare,tab1.DataOraSosire
 
left join
	(	SELECT IdMasina,AVG(CombustibilConsumat) As MoyenneCombustible
		FROM FoaieParcursMasina
		GROUP BY IdMasina
	)	x on x.IdMasina = tab1.IdMasina
 
left join TipuriAutomobile tab2 on tab1.IdMasina = tab2.IdMasina
 
-- idtip unic ok, tipcombustibil unic ok, media pe fiecare tip combustibil??
 
-- je veux la moyenne du combustible d'une periode la periode est represente par les champs DataOraPlecare et DataOraSosire
WHERE DataOraPlecare >'1/1/2005' AND DataOraPlecare<'12/12/2009' AND DataOraSosire  >'1/1/2005'  AND DataOraSosire<'12/12/2009' 
GROUP BY tab2.IdTip,tab2.TipCombustibil

Code :

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

1
2
3
4
5
 
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'left'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'x'.

tab1 - alias pour table FoaieParcursMasina

tab2- alias pour table TipuriAutomobile.

TipAutomobil -colonne qui represente le nom du type d'automobile.

IdTip - cle primaire qui identifie uniquement table TipuriAutomobile.

IdFoaie- cle primaire table FoaieParcursMasina

CombustibilConsumat - colonne qui represente la valeur du combustible consomme.

[elsuket]

Bonjour,

Le squelette d'une requête SELECT utilisant une fonction d'agrégat est le suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT A.mesColonnesDeA,
	B.mesColonnesDeB,
	fonctionAgregat(A.uneColonneDeA) -- ou B.uneColonneDeB
FROM monSchema.maTableA AS A
[INNER | LEFT | RIGHT] JOIN monSchema.maTableB AS B
	ON A.uneColonneDeA = B.uneColonneDeB
WHERE A.colonnesDeA = ...
AND A.colonnesDeA = ...
AND B.colonnesDeB = ...
GROUP BY A.mesColonnesDeA, B.mesColonnesDeB
ORDER BY A.mesColonnesDeA, B.mesColonnesDeB

On doit donc toujours avoir les jointures entre le FROM et le GROUP BY, ce qui explique que tu aies des erreurs de syntaxe.

Mais tu peux écrire ta requête plus simplement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT HISTO_MASINA.DataOraPlecare,
		HISTO_MASINA.DataOraSosire,
		TYPE_MASINA.TipAutomobil,
		TYPE_MASINA.TipCombustibil,
		AVG(CombustibilConsumat) AS avgCombustibilConsumat
FROM dbo.FoaieParcursMasina AS HISTO_MASINA
JOIN dbo.PatrimoniuMasini AS MASINI
	ON HISTO_MASINA.IdMasina = MASINI.IdMasina
JOIN dbo.TipuriAutomobile AS TYPE_MASINA
	ON MASINI.IdMasina = TYPE_MASINA.IdMasina
GROUP BY HISTO_MASINA.DataOraPlecare,
		HISTO_MASINA.DataOraSosire,
		TYPE_MASINA.TipAutomobil,
		TYPE_MASINA.TipCombustibil

Je n'ai pas compris pourquoi tu as utilisé LEFT JOIN

@++

[mihaispr]

Merci beacoup elsuket pour ton reponse et tes explications!


Quelle est la difference entre join et left join?

Si on a tab1.id, tab2.nom et tab2.prenom quand on fait left join il fait ca:

Code :

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

1
2
 
id  nom  prenom

J'ai compris l'utilisation de left join en sql server.

Ce que je ne sais c'est quelle est la difference entre les 2 et pourquoi tu n'as pas fait left join.


En fait mon souci c'est comment integrer cette requete dans une procedure stoque ayant ces parametres. (donc creer une procedure stoque qui contient des joins et une fonction agregat avg).

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
 
 
CREATE PROCEDURE ProcJoinEtAgregat
@DataOraPlecare datetime,
@DataOraSosire datetime,
@TipAutomobil varchar(255),
@TipCombustibil varchar(255)
 
AS
 
BEGIN
--requete apres le mot reserve AS)
 
		AVG(CombustibilConsumat) AS avgCombustibilConsumat
FROM dbo.FoaieParcursMasina AS HISTO_MASINA
JOIN dbo.PatrimoniuMasini AS MASINI
	ON HISTO_MASINA.IdMasina = MASINI.IdMasina
JOIN dbo.TipuriAutomobile AS TYPE_MASINA
	ON MASINI.IdMasina = TYPE_MASINA.IdMasina
GROUP BY HISTO_MASINA.DataOraPlecare,
		HISTO_MASINA.DataOraSosire,
		TYPE_MASINA.TipAutomobil,
		TYPE_MASINA.TipCombustibil
END
GO

Erreur a la ligne:

Code :

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

1
2
 
AVG(CombustibilConsumat) AS avgCombustibilConsumat

Code :

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

1
2
3
 
Msg 102, Level 15, State 1, Procedure ProcJoinEtAgregat, Line 11
Incorrect syntax near 'AVG'.

[/dev/null]

Alors, la difference entre INNER JOIN et LEFT JOIN (ou RIGHT JOIN):
Prenon l'exemple fictif d'une BDD de gestion client.
Imagine que tu veuilles la liste total des personnes et leurs commandes, tu utiliseras un LEFT ou RIGTH JOIN (si ta table client et écrit à gauche dans ta requette, c'est un LEFT JOIN, si la table est écrit a droit, c'est un RIGHT JOIN)
Si tu desire la liste des perssones AYANT une commande, tu feras un INNER JOIN.

INNER: La clause ON doit obligatoiremant être vérifié
LEFT: Tous les enregistrement de la table de gauche et accésoirement, les enregitrement de la table a droite vérifiant la clause ON. Retourne NULL pour les champs de la table de droite si la clause ON n'est pas vérifié
RIGTH: Pareille que LEFT, mais a tous ceux a droite et accésoirement ceux a gauches

Pour ta procedure stoqué:

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
 
CREATE PROCEDURE ProcJoinEtAgregat
@DataOraPlecare datetime,
@DataOraSosire datetime,
@TipAutomobil varchar(255),
@TipCombustibil varchar(255)
 
AS
 
BEGIN
--requete apres le mot reserve AS)
 
SELECT AVG(CombustibilConsumat) AS avgCombustibilConsumat
FROM dbo.FoaieParcursMasina AS HISTO_MASINA
JOIN dbo.PatrimoniuMasini AS MASINI
	ON HISTO_MASINA.IdMasina = MASINI.IdMasina
JOIN dbo.TipuriAutomobile AS TYPE_MASINA
	ON MASINI.IdMasina = TYPE_MASINA.IdMasina
WHERE DataOraPlecare > @DataOraPlecare AND DataOraPlecare<@DataOraPlecare AND DataOraSosire  > @DataOraSosire AND DataOraSosire< @DataOraSosire AND  tab2.IdTip = @TipAutomobil  AND tab2.TipCombustibil = @TipCombustibil 
GROUP BY tab2.IdTip,tab2.TipCombustibil
	HISTO_MASINA.DataOraPlecare,
	HISTO_MASINA.DataOraSosire,
	TYPE_MASINA.TipAutomobil,
	TYPE_MASINA.TipCombustibil
END
GO

Ou un truc du genre... mais je ne comprend pas bien ta nomination des champ je ne comprend pas le roumin ^^.
Cette version ne marcheras pas car la clause "DataOraSosire > @DataOraSosire AND DataOraSosire< @DataOraSosire" est impossible ^^.

[mihaispr]

Merci beacoup pour m'expliquer en details la difference entre inner join,left join , right join j'ai trouve aussi un materiel visuel avec des
lot (multimi en roumain).


Oui le probleme est que je ne sais pas comment creer une procedure stoque en sql server (qui contient 2 joins et une fonction agregat AVG).

Si vous des idees je suis preneur!



Merci en avance a tous pour avoir pris le temps de me lire et de me repondre!

[/dev/null]

Je pense que ça, ça marcheras:

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
 
CREATE PROCEDURE ProcJoinEtAgregat
@StarDate datetime,
@EndDate datetime,
@TipAutomobil varchar(255),
@TipCombustibil varchar(255)
 
AS
BEGIN
 
SELECT AVG(CombustibilConsumat) AS avgCombustibilConsumat
FROM dbo.FoaieParcursMasina AS HISTO_MASINA
	INNER JOIN dbo.PatrimoniuMasini AS MASINI
		ON HISTO_MASINA.IdMasina = MASINI.IdMasina
	INNER JOIN dbo.TipuriAutomobile AS TYPE_MASINA
		ON MASINI.IdMasina = TYPE_MASINA.IdMasina
WHERE (DataOraPlecare >= @StartDate AND DataOraPlecare <= @EndDate)
	AND
	(DataOraSosire >= @StartDate AND DataOraSosire <= @EndDate)
	AND
	(tab2.IdTip = @TipAutomobil  AND tab2.TipCombustibil = @TipCombustibil)
GROUP BY tab2.IdTip,
	tab2.TipCombustibil
	HISTO_MASINA.DataOraPlecare,
	HISTO_MASINA.DataOraSosire,
	TYPE_MASINA.TipAutomobil,
	TYPE_MASINA.TipCombustibil
END
GO