oracle 分组去空,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