1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| with donnees_j as (select 100 station, to_date('24/09/2009', 'DD/MM/YYYY') dat, 10 temperature from dual union all
select 100 station, to_date('24/09/2008', 'DD/MM/YYYY') dat, 15 temperature from dual union all
select 100 station, to_date('24/09/2007', 'DD/MM/YYYY') dat, 22 temperature from dual union all
select 100 station, to_date('24/09/2006', 'DD/MM/YYYY') dat, 12 temperature from dual union all
select 100 station, to_date('24/09/2005', 'DD/MM/YYYY') dat, 10 temperature from dual)
select j.station,
max(j.dat),
max(decode(histo.an, 0, j.temperature)) temp_0,
max(decode(histo.an, 1, j.temperature)) temp_1,
max(decode(histo.an, 2, j.temperature)) temp_2,
max(decode(histo.an, 3, j.temperature)) temp_3,
max(decode(histo.an, 4, j.temperature)) temp_4
from donnees_j j,
(select level-1 an from dual connect by level <= 5) histo
where j.dat = add_months(to_date('24/09/2009', 'DD/MM/YYYY'), -histo.an*12)
group by j.station; |
Partager