依據前後資料關聯關係，生成多行資料

pwz1688發表於2014-03-06
例：一張表的資料如下，第一個欄位是日期，第二個欄位是數字 
ROW DATE NUMBER
1 2014-02-01 4
2 2014-02-08 3
3 2014-02-15 4
4 2014-02-22 9
現在需要寫一個查詢，顯示出的結果是7號以前的的值是4,8號到14號的值是3,15號到21號的值是4,21號到當前日期的值是9。
方法1：
分析：要生成的資料，日期非常有規律，以一個星期為界限，可以構造一個序列，0-6，然後已有日期+序列，作為新生成的日期，
完整sql程式碼如下：
SQL> edit
已寫入 file afiedt.buf
  1 with t1 as(
  2 select date'2014-02-01' dt,4 num from dual union all
  3 select date'2014-02-08',3 from dual union all
  4 select date'2014-02-15',4 from dual union all
  5 select date'2014-02-22',9 from dual),
  6 t2 as(select level-1 l from dual connect by
  7 level<=7)
  8* select t1.dt+l,num from t1,t2 order by 1
  9 /
T1.DT+L NUM
-------------- ----------
01-2月 -14 4
02-2月 -14 4
03-2月 -14 4
04-2月 -14 4
05-2月 -14 4
06-2月 -14 4
07-2月 -14 4
08-2月 -14 3
09-2月 -14 3
10-2月 -14 3
11-2月 -14 3
T1.DT+L NUM
-------------- ----------
12-2月 -14 3
13-2月 -14 3
14-2月 -14 3
15-2月 -14 4
16-2月 -14 4
17-2月 -14 4
18-2月 -14 4
19-2月 -14 4
20-2月 -14 4
21-2月 -14 4
22-2月 -14 9
T1.DT+L NUM
-------------- ----------
23-2月 -14 9
24-2月 -14 9
25-2月 -14 9
26-2月 -14 9
27-2月 -14 9
28-2月 -14 9
已選擇28行。
方法2：
先構造一個日期表，然後與此表left join，再利用last_value( ignore nulls)生成num欄位資料，完整程式碼如下：
SQL> edit
已寫入 file afiedt.buf
  1 with t1 as
  2 (select date '2014-02-01' dt, 4 num
  3 from dual
  4 union all
  5 select date '2014-02-08', 3
  6 from dual
  7 union all
  8 select date '2014-02-15', 4
  9 from dual
 10 union all
 11 select date '2014-02-22', 9 from dual),
 12 t2 as
 13 (select date '2014-02-01' + level - 1 dt
 14 from dual
 15 connect by level <=
 16 TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('201402', 'YYYYMM')), 'dd')))
 17 select t2.dt, last_value(num ignore nulls) over(order by t2.dt) num
 18 from t2
 19 left join t1
 20* on (t1.dt = t2.dt)
SQL> /
DT NUM
-------------- ----------
01-2月 -14 4
02-2月 -14 4
03-2月 -14 4
04-2月 -14 4
05-2月 -14 4
06-2月 -14 4
07-2月 -14 4
08-2月 -14 3
09-2月 -14 3
10-2月 -14 3
11-2月 -14 3
DT NUM
-------------- ----------
12-2月 -14 3
13-2月 -14 3
14-2月 -14 3
15-2月 -14 4
16-2月 -14 4
17-2月 -14 4
18-2月 -14 4
19-2月 -14 4
20-2月 -14 4
21-2月 -14 4
22-2月 -14 9
DT NUM
-------------- ----------
23-2月 -14 9
24-2月 -14 9
25-2月 -14 9
26-2月 -14 9
27-2月 -14 9
28-2月 -14 9
已選擇28行。
SQL> 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1102669/，如需轉載，請註明出處，否則將追究法律責任。

相關文章