一条语句获得一年所有月份
经常会碰到一些oracle的需求,例如在生成本年1-12月份的年月记录,然后再和业务表进行left join,以此获得本年月度的一些数字的分布
这里说说如何快速的生成1-12月份的年月记录,关键是如下这条sql
select rownum as rn from dual connect by rownum<13
写道
select a.my_year||(case when b.my_month<10 then '0'||to_char(b.my_month)
else to_char(b.my_month) end) year_month
from
(
select to_char(sysdate,'yyyy') my_year
from dual
) a
cross join (select rownum as my_month from dual connect by rownum<13) b
else to_char(b.my_month) end) year_month
from
(
select to_char(sysdate,'yyyy') my_year
from dual
) a
cross join (select rownum as my_month from dual connect by rownum<13) b