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
|
/* Jeu de données si on n'a pas les tables dans la base de données
with toto (Pays, Plage_Sup, Nb_Personnes) as
(
select 'Fr', 5, 100
union all
select 'Fr', 10, 5000
union all
select 'Fr', 15, 14500
union all
select 'Fr', 20, 500
union all
select 'Fr', 25, 6000
union all
select 'Fr', 30, 5000
union all
select 'Fr', 35, 1000
union all
select 'Fr', 40, 500
union all
select 'Fr', 50, 10000
),
tata (Pays, Plage_Sup) as
(
select 'Fr', 5
union all
select 'Fr', 20
union all
select 'Fr', 30
union all
select 'Fr', 35
union all
select 'Fr', 50
)
*/
select to2.pays, tmp.plage_sup, sum(to2.nb_personnes) nb
from toto to2
inner join (
select ta1.pays, ta1.Plage_Sup, coalesce(max(ta2.plage_sup), 0) m
from tata ta1
left outer join tata ta2 on ta2.pays = ta1.pays and ta2.Plage_Sup < ta1.Plage_Sup
group by ta1.pays, ta1.Plage_Sup
) tmp on tmp.Pays = to2.Pays and to2.Plage_Sup > m and to2.Plage_Sup <= tmp.Plage_Sup
group by to2.pays, tmp.plage_sup; |
Partager