外关联sql



SELECT LPAD(level,2,'0') FROM DUAL CONNECT BY level <=12;
 select t2.pdate, nvl(t1.menergy, 0) as result
   from (select to_number((year || month)) as showtime,
                nvl(menergy, 0) as menergy
           from t_lf_st_stat_mcharact_96
          where year >= '2010'
            and year <= '2014'
            and month >= '02'
            and month <= '02'
            and netid = '01'
          order by showtime) t1,
        (select a.pyear || b.pmonth as pdate
           from (select '2010' + LEVEL - 1 as pyear
                   from dual
                 connect by LEVEL <= '2014' - '2010' + 1) a,
                (select LPAD(monthvalue, 2, '0') as pmonth
                   from (select 1 + LEVEL - 1 as monthvalue
                           from dual
                         connect by LEVEL <= 12)
                  where monthvalue >= '02'
                    and monthvalue <= '02') b) t2
  where t1.showtime(+) = t2.pdate
  order by t2.pdate