A表：
姓名 开始日期 结束日期
张三 2012-04-01 2012-04-03
李四 2012-04-01 2012-04-02
王五 2012-04-05 2012-04-06
求一sql（统计汇总），将A表转换成如下表
结果表：
总人数 日期
2 2012-04-01
2 2012-04-02
1 2012-04-03
1 2012-04-05
1 2012-04-06
------解决方案--------------------
- SQL code
SQL codewith a as(select '张三' xm,to_date('2012-04-01','yyyy-mm-dd') ksrq,to_date('2012-04-03','yyyy-mm-dd') jsrq from dualunion allselect '李四',to_date('2012-04-01','yyyy-mm-dd'),to_date('2012-04-02','yyyy-mm-dd') from dualunion allselect '王五',to_date('2012-04-05','yyyy-mm-dd'),to_date('2012-04-06','yyyy-mm-dd') from dual)select count(1), ksrq + rn - 1 from a, (select rownum rn from dual connect by rownum <= (select max(jsrq - ksrq) from a)) t where a.jsrq >= a.ksrq + rn - 1 group by ksrq + rn - 1 COUNT(1) KSRQ+RN-1---------- ----------- 2 2012-4-1 2 2012-4-2 1 2012-4-3 1 2012-4-5 1 2012-4-6
------解决方案--------------------
- SQL code
with a as(select '张三' xm,to_date('2012-04-01','yyyy-mm-dd') b_date,to_date('2012-04-03','yyyy-mm-dd') e_date from dualunion allselect '李四',to_date('2012-04-01','yyyy-mm-dd'),to_date('2012-04-02','yyyy-mm-dd') from dualunion allselect '王五',to_date('2012-04-05','yyyy-mm-dd'),to_date('2012-04-06','yyyy-mm-dd') from dual)select count(1) 总人数,d_date 日期from a,(select date'2012-04-01'+level-1 d_datefrom dualconnect by level <= 30) bwhere b.d_date <= a.e_date and b.d_date >= a.b_dategroup by d_dateorder by d_date 总人数 日期---------------------------------1 2 2012/4/12 2 2012/4/23 1 2012/4/34 1 2012/4/55 1 2012/4/6