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
| select 'groupe1' as groupe, equipe, joue, gagne, nul, perdu, diff, 3 * gagne + nul as pts, (
select count(*)
from groupe1 g1
where (3 * g1.gagne + g1.nul) > (3 * g.gagne + g.nul)
or ((3 * g1.gagne + g1.nul) = (3 * g.gagne + g.nul) and g1.diff > g.diff)
) + 1 as rang
from groupe1 g
union all
select 'groupe2' as groupe, equipe, joue, gagne, nul, perdu, diff, 3 * gagne + nul as pts, (
select count(*)
from groupe2 g1
where (3 * g1.gagne + g1.nul) > (3 * g.gagne + g.nul)
or ((3 * g1.gagne + g1.nul) = (3 * g.gagne + g.nul) and g1.diff > g.diff)
) + 1 as rang
from groupe2 g
union all
select 'groupe3' as groupe, equipe, joue, gagne, nul, perdu, diff, 3 * gagne + nul as pts, (
select count(*)
from groupe3 g1
where (3 * g1.gagne + g1.nul) > (3 * g.gagne + g.nul)
or ((3 * g1.gagne + g1.nul) = (3 * g.gagne + g.nul) and g1.diff > g.diff)
) + 1 as rang
from groupe3 g
union all
select 'groupe4' as groupe, equipe, joue, gagne, nul, perdu, diff, 3 * gagne + perdu as pts, (
select count(*)
from groupe4 g1
where (3 * g1.gagne + g1.nul) > (3 * g.gagne + g.nul)
or ((3 * g1.gagne + g1.nul) = (3 * g.gagne + g.nul) and g1.diff > g.diff)
) + 1 as rang
from groupe4 g
order by groupe, pts desc, diff desc |
Partager