日期:2014-05-17 浏览次数:20960 次
select rn||'月份' ,count(dat)||'(个人)' from (select rownum rn from dual connect by rownum <=12) t1 , (select substr(to_char(登记时间,'YYYYMMDD'),5,2) dat from a)t2 where t1.rn=t2.dat(+) group by rn order by rn
------解决方案--------------------
WITH T1 AS( SELECT ADD_MONTHS(DATE'2010-12-01',LEVEL) AS t_date FROM DUAL CONNECT BY LEVEL <= 12 ),T2 AS(SELECT '张三' 姓名,date'2011-12-23' 时间 , '王武' 登记人 FROM DUAL UNION ALL SELECT '李四' 姓名,date'2011-12-25' 时间 , '刘敏' 登记人 FROM DUAL UNION ALL SELECT '周松' 姓名,date'2011-11-24' 时间 , '王武' 登记人 FROM DUAL UNION ALL SELECT '赵思' 姓名,date'2011-10-22' 时间 , '王武' 登记人 FROM DUAL UNION ALL SELECT '赵思' 姓名,date'2011-10-01' 时间 , '王武' 登记人 FROM DUAL UNION ALL SELECT '钱斯' 姓名,date'2011-04-06' 时间 , '张松' 登记人 FROM DUAL UNION ALL SELECT '周松' 姓名,date'2011-03-24' 时间 , '王武' 登记人 FROM DUAL UNION ALL SELECT '钱斯' 姓名,date'2011-01-06' 时间 , '张松' 登记人 FROM DUAL )SELECT TO_CHAR(T1.t_date,'YYYY-MM') 月份,NVL(count(T2.时间),0)||'(个人)' 总人数 FROM T1 LEFT JOIN T2 ON TO_CHAR(T1.t_date,'YYYY-MM') = TO_CHAR(T2.时间,'YYYY-MM') GROUP BY TO_CHAR(T1.t_date,'YYYY-MM') ORDER BY TO_CHAR(T1.t_date,'YYYY-MM') ------------------------------------------------- 月份 总人数 1 2011-01 1(个人) 2 2011-02 0(个人) 3 2011-03 1(个人) 4 2011-04 1(个人) 5 2011-05 0(个人) 6 2011-06 0(个人) 7 2011-07 0(个人) 8 2011-08 0(个人) 9 2011-09 0(个人) 10 2011-10 2(个人) 11 2011-11 1(个人) 12 2011-12 2(个人)