oracle-按月、日、时分组查询数据，为空的数据补零

------月

select nvl(t1.tvalue, 0) "data1", t2.datevalue "name"
  from (select sum(t.TSAI03) tvalue, TO_CHAR(t.TSAI01, 'yyyy-mm') timevalue
          from TSA009 t
         where TO_CHAR(t.TSAI01, 'YYYY-MM-DD') like '2012%'
           and t.unit_code like '411500A0050000'
         group by TO_CHAR(t.TSAI01, 'yyyy-mm')) t1,
       (select '2012-' || lpad(level, 2, 0) datevalue
          from dual
        connect by level < 13) t2
 where t1.timevalue(+) = t2.datevalue
 order by t2.datevalue

 

-----日

select nvl(t1.tvalue, 0) "data1", t2.datevalue "name"
  from (select sum(t.TSAI03) tvalue,
               TO_CHAR(t.TSAI01, 'yyyy-mm-dd') timevalue
          from TSA009 t
         where TO_CHAR(t.TSAI01, 'YYYY-MM-DD') like '2012-04%'
           and t.unit_code like '411500A0050000'
         group by TO_CHAR(t.TSAI01, 'yyyy-mm-dd')) t1,
       (select '2012-04-' || lpad(level, 2, 0) datevalue
          from dual
        connect by level < (select to_number(substr(last_day(to_date('2012-04-10',
                                                                     'yyyy-mm-dd')),
                                                    0,
                                                    2))
                              from dual) + 1) t2
 where t1.timevalue(+) = t2.datevalue
 order by t2.datevalue

 

----时

select nvl(t1.tvalue, 0) "data1", t2.datevalue "name"
  from (select sum(t.TSAJ03) tvalue,
               TO_CHAR(t.TSAJ01, 'yyyy-mm-dd hh24') timevalue
          from TSA010 t
         where TO_CHAR(t.TSAJ01, 'YYYY-MM-DD') like '2012-04-10%'
           and t.unit_code like '411500A0050000'
         group by TO_CHAR(t.TSAJ01, 'yyyy-mm-dd hh24')) t1,
       (select '2012-04-10 ' || lpad(level, 2, 0) datevalue
          from dual
        connect by level < 25) t2
 where t1.timevalue(+) = t2.datevalue
 order by t2.datevalue

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/GenghisKhan/archive/2012/07/10/2584571.html