求租一个连续日期多记录的报表sql！

oraclemch 2009-12-08 08:34:59

有某个表A，字段以及数据如下
--do_time id status num start_time end_time
2009-12-2 21594 10 1 2009-12-2 2009-12-3
2009-12-6 21951 10 1 2009-12-6 2009-12-7
2009-12-7 21960 10 1 2009-12-7 2009-12-8
2009-12-7 23059 12 1 2009-12-7 2009-12-11
2009-12-7 23061 23 1 2009-12-7 2009-12-12

大家看到，有个do_time执行时间，还有一个start_time执行开始时间、end_time执行结束时间。num执行标记1（1为已执行，0未执行）
执行时间do_time一定是在start_time执行开始时间与end_time执行结束时间之间的某一天的。

现在的记录都是单条的，比如
2009-12-7 23059 12 0 2009-12-7 2009-12-11
记录
现在要变成5条记录，这5条记录就是从执行开始时间到执行结束时间，每一天一条记录。而且这些记录除了do_time字段跟num执行标记不一样以外，
剩下的字段数据都是一样的。
2009-12-7 23059 12 1 2009-12-7 2009-12-11
2009-12-8 23059 12 0 2009-12-7 2009-12-11
2009-12-9 23059 12 0 2009-12-7 2009-12-11
2009-12-10 23059 12 0 2009-12-7 2009-12-11
2009-12-11 23059 12 0 2009-12-7 2009-12-11

而且，每条记录都需要变成这样的，简单点说，就是加上未执行的天数的记录。
最后我要变成这样的数据如下：
2009-12-2 21594 10 1 2009-12-2 2009-12-3 --
2009-12-3 21594 10 0 2009-12-2 2009-12-3
2009-12-6 21951 10 1 2009-12-6 2009-12-7 --
2009-12-7 21951 10 0 2009-12-6 2009-12-7
2009-12-7 21960 10 1 2009-12-7 2009-12-8 --
2009-12-8 21960 10 0 2009-12-7 2009-12-8
2009-12-7 23059 12 1 2009-12-7 2009-12-11 --
2009-12-8 23059 12 0 2009-12-7 2009-12-11
2009-12-9 23059 12 0 2009-12-7 2009-12-11
2009-12-10 23059 12 0 2009-12-7 2009-12-11
2009-12-11 23059 12 0 2009-12-7 2009-12-11
2009-12-7 23061 23 1 2009-12-7 2009-12-12 --
2009-12-8 23061 23 0 2009-12-7 2009-12-12
2009-12-9 23061 23 0 2009-12-7 2009-12-12
2009-12-10 23061 23 0 2009-12-7 2009-12-12
2009-12-11 23061 23 0 2009-12-7 2009-12-12
2009-12-12 23061 23 0 2009-12-7 2009-12-12

我可以通过select ... from dual connect by ...之类的语句得到2009-12-7到2009-12-12之间所有的天数数据，
但是不知道如何跟原来的2009-12-7 23061 23 0 2009-12-7 2009-12-12结合起来形成一条记录。

大家熟悉，懂的，给点意见或者实例吧！谢了！

...全文

162 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

hflxwnet 2010-01-29

打赏
举报

ddddddddd

oraclemch 2009-12-09

打赏
举报

谢谢 wildwave！

a9529lty 2009-12-08

打赏
举报

WITH 

TMP_A AS (

 SELECT A.DO_TIME,

        A.ID,

        A.STATUS,

        A.NUM,

        A.START_TIME,

        A.END_TIME,

        A.END_TIME - A.START_TIME + 1 AS DATE_B

  FROM A

),

TMP_MAXDAY AS (

 SELECT MAX(A.END_TIME - A.START_TIME + 1) AS DATE_B

   FROM A

),

TMP_DATE AS (

   SELECT LEVEL - 1 AS TMP_LEVEL 

     FROM DUAL,TMP_MAXDAY 

  CONNECT BY LEVEL <= TMP_MAXDAY.DATE_B

)



SELECT TMP_A.DO_TIME + TMP_DATE.TMP_LEVEL AS DO_TIME,

       TMP_A.ID,

       TMP_A.STATUS,

       CASE WHEN TMP_DATE.TMP_LEVEL > 0 THEN 0

       ELSE TMP_A.NUM END AS NUM,

       TMP_A.START_TIME,

       TMP_A.END_TIME

FROM

TMP_A,

TMP_DATE

WHERE

TMP_DATE.TMP_LEVEL < TMP_A.DATE_B

小灰狼W 2009-12-08

打赏
举报

with a as(
select date'2009-12-2' do_time,21594 id,10 status,1 num,date'2009-12-2' start_time,date'2009-12-3' end_time from dual
union all select date'2009-12-6',21951,10,1,date'2009-12-6',date'2009-12-7' from dual
union all select date'2009-12-7',21960,10,1,date'2009-12-7',date'2009-12-8' from dual
union all select date'2009-12-7',23059,12,1,date'2009-12-7',date'2009-12-11' from dual
union all select date'2009-12-7',23061,23,1,date'2009-12-7',date'2009-12-12' from dual)

select a.do_time+b.rn do_time,a.id,a.status,
case when a.start_time+b.rn>a.do_time then 0 else 1 end num,
a.start_time,a.end_time from a,
(select rownum-1 rn from dual connect by rownum<=(select max(end_time-start_time)+1 from a))b
where a.end_time-a.start_time>=b.rn
order by 2,1