问题出处:http://topic.csdn.net/u/20091208/20/f8c6fb55-fcd9-4e1b-a798-855f7a44166d.html?68415
有某个表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结合起来形成一条记录。
大家熟悉,懂的,给点意见或者实例吧!谢了!
- 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