Solution amélioré, sans le group by rollup
Salut,
J'ai trouvé un moyen plus simple et donne le meme le résultat ,pour éviter le group by rollup (qui diminuait la performance).
1- Solution
Code:
1 2 3 4 5 6 7 8 9
|
SELECT T.id_affaire, T.id_mvt, T.mt_mvt, sum(T.mt_reg) mt_reg
FROM
(SELECT mvt.id_affaire, mvt.id_mvt, sum(mt_reg) mt_reg,
sum(mt_mvt) mt_mvt
FROM mvt LEFT OUTER JOIN reg ON mvt.id_mvt = reg.id_mvt
AND mvt.id_affaire = reg.id_affaire
GROUP BY mvt.id_affaire, mvt.id_mvt, reg.id_reg) T
GROUP BY T.id_affaire, T.id_mvt, T.mt_mvt |
2-Solution
Code:
1 2 3 4 5 6 7 8 9 10
|
SELECT
mvt.id_affaire,
mvt.id_mvt,sum(mt_mvt) mt_mvt ,
(SELECT SUM(mt_reg)
FROM REG
WHERE reg.id_affaire = mvt.id_affaire
AND reg.id_mvt = mvt.id_mvt) mt_reg
FROM mvt
GROUP BY mvt.id_affaire, mvt.id_mvt |