col 年齢 for 99
--■■■case式を使う方法■■■
select 名前,誕生日,指定年月,
trunc(months_between(指定年月,
case when to_char(to_date(to_char(指定年月,'yyyy') || '1231'),'ddd') = '365'
and to_char(誕生日,'mmdd') = '0229'
then to_date(to_char(誕生日,'yyyy') || '0301','yyyymmdd')
else 誕生日 end) /12) as 年齢
from 誕生日テーブル,
(select to_date('2004/02/27','yyyy/mm/dd') as 指定年月 from dual
union select to_date('2004/02/28','yyyy/mm/dd') from dual
union select to_date('2004/02/29','yyyy/mm/dd') from dual
union select to_date('2004/03/01','yyyy/mm/dd') from dual
union select to_date('2004/03/02','yyyy/mm/dd') from dual
union select to_date('2005/02/27','yyyy/mm/dd') from dual
union select to_date('2005/02/28','yyyy/mm/dd') from dual
union select to_date('2005/03/01','yyyy/mm/dd') from dual
union select to_date('2005/03/02','yyyy/mm/dd') from dual
union select to_date('2006/01/01','yyyy/mm/dd') from dual
union select to_date('2006/12/31','yyyy/mm/dd') from dual)
order by 名前,指定年月;
--■■■trunc関数を使う方法■■■
select 名前,誕生日,指定年月,
trunc((to_number(to_char(指定年月,'YYYYMMDD'))-to_number(to_char(誕生日,'YYYYMMDD')))/10000) as 年齢
from 誕生日テーブル,
(select to_date('2004/02/27','yyyy/mm/dd') as 指定年月 from dual
union select to_date('2004/02/28','yyyy/mm/dd') from dual
union select to_date('2004/02/29','yyyy/mm/dd') from dual
union select to_date('2004/03/01','yyyy/mm/dd') from dual
union select to_date('2004/03/02','yyyy/mm/dd') from dual
union select to_date('2005/02/27','yyyy/mm/dd') from dual
union select to_date('2005/02/28','yyyy/mm/dd') from dual
union select to_date('2005/03/01','yyyy/mm/dd') from dual
union select to_date('2005/03/02','yyyy/mm/dd') from dual
union select to_date('2006/01/01','yyyy/mm/dd') from dual
union select to_date('2006/12/31','yyyy/mm/dd') from dual)
order by 名前,指定年月;