分组补空值问题

 http://topic.csdn.net/u/20110722/15/14f831ce-bb72-44c9-b0be-70d1ba0bf6a2.html?81007
 
 我测试通过的sql
 SELECT TO_CHAR(DAYOFMONTH, 'yyyy-mm-dd'), NVL(sum(b), 0)
  FROM tb
 RIGHT JOIN (SELECT SYSDATE - (LEVEL - 1) AS DAYOFMONTH
               FROM DUAL
             CONNECT BY LEVEL <= 60) ON TO_CHAR(time, 'yyyy-mm-dd') = TO_CHAR(DAYOFMONTH,'yyyy-mm-dd')
 where DAYOFMONTH >= TO_DATE('2011-07-01','yyyy-mm-dd')
 GROUP BY TO_CHAR(DAYOFMONTH, 'yyyy-mm-dd')
 ORDER BY TO_CHAR(DAYOFMONTH, 'yyyy-mm-dd') ASC
 

输出
2011-07-01 0
2011-07-02 0
2011-07-03 0
2011-07-04 12
……
2011-07-14 13
……

 CREATE TABLE TB
(
  A     INTEGER,
  B     INTEGER,
  C     INTEGER,
  TIME  DATE
)

http://topic.csdn.net/u/20110721/17/913972e9-4c21-4c42-8517-8266760f94c1.html
我测试通过的sql
with t1 as(
  select '2011-'||lpad(level,2,0) a
    from dual connect by level <= 12
)select t1.a,nvl(sum(t2.num),0)
   from tb2 t2
   RIGHT JOIN t1 on t1.a = to_char(t2.curdate,'yyyy-mm')
  group by t1.a;
 
结果
2011-01 0
2011-02 20
2011-03 0
2011-04 20
……
2011-06 70
……

CREATE TABLE TB2
(
  ID       INTEGER,
  CURDATE  DATE,
  NUM      INTEGER
)