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
|
DECLARE @Partiel TABLE (ID_Partiel Int, ID_UE Int, Note Decimal(10,4), Coefficient Decimal(10,4) )
DECLARE @UE TABLE(ID_UE Int, Libelle Varchar(50) )
INSERT @UE VALUES( 1, 'Math' )
INSERT @UE VALUES( 2, 'Physique' )
INSERT @UE VALUES( 3, 'Bio' )
INSERT @Partiel VALUES( 10, 1, 15, 10 )
INSERT @Partiel VALUES( 11, 1, 10, 1 )
INSERT @Partiel VALUES( 20, 2, 5, 2 )
INSERT @Partiel VALUES( 21, 2, 9, 1 )
INSERT @Partiel VALUES( 30, 3, 12, 1 )
INSERT @Partiel VALUES( 31, 3, 14, 1 )
SELECT Sum( Note * Coefficient )/Sum(Coefficient) As MoyenneGenerale FROM @Partiel P
SELECT M.Libelle, N.Moyenne
FROM (
SELECT P.ID_UE, Sum( Note * Coefficient )/Sum(Coefficient) As Moyenne
FROM @Partiel P
GROUP BY P.ID_UE
) N
INNER JOIN @UE M
ON ( M.ID_UE = N.ID_UE ) |
Partager