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
姓名 开始日期 结束日期
张三 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
with a as(
select '张三' xm,to_date('2012-04-01','yyyy-mm-dd') ksrq,to_date('2012-04-03','yyyy-mm-dd') jsrq from dual
union all
select '李四',to_date('2012-04-01','yyyy-mm-dd'),to_date('2012-04-02','yyyy-mm-dd') from dual
union all
select '王五',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
COUNT(1) KSRQ+RN-1
---------- -----------
2 2012-4-1
2 2012-4-2
1 2012-4-5
1 2012-4-6
2012-4-3的数据没有
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 dual
union all
select '李四',to_date('2012-04-01','yyyy-mm-dd'),to_date('2012-04-02','yyyy-mm-dd') from dual
union all
select '王五',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_date
from dual
connect by level <= 30) b
where b.d_date <= a.e_date and b.d_date >= a.b_date
group by d_date
order by d_date 总人数 日期
---------------------------------
1 2 2012/4/1
2 2 2012/4/2
3 1 2012/4/3
4 1 2012/4/5
5 1 2012/4/6
以下是我sql,满足LZ所描述的需求,且不固定源数据!select count(1) 总人数, rq 日期 from (select my.*, b.rq
from my
left join (select to_char(sysdate - level, 'yyyy-mm-dd') rq from dual
connect by date '2012-01-01' + level < date '2012-10-01') b
on my.开始日期 <= b.rq
and my.结束日期 >= b.rq
) group by rq order by 2; 总人数 日期
---------------------------------
1 2 2012-04-01
2 2 2012-04-02
3 1 2012-04-03
4 1 2012-04-05
5 1 2012-04-06
其中B就是生成一段连续的日期。
from dual
connect by level <= 30,这部分写死了,‘2012-04-01’这个日期只是举例说明而已,是整个b_date中最小的值,不固定,谢谢(和lt870730439说的且不固定源数据应该是这个道理)
from a,
(select t.b_date+level-1 d_date
from dual,(select min(b_date) b_date,max(e_date) e_date from a) t
connect by level <= t.e_date-t.b_date+1) b
where b.d_date <= a.e_date and b.d_date >= a.b_date
group by d_date
order by d_date