请教SQL----时间可以固定一个时间段

Question

源表：
biz_dt    secu_code  matched_num
20100901  10001      100
20100903  10001      101
20100906  10001      104
20100901  10002      107
20100902  10002      103
20100906  10002      117
需要得到下面的目标表：
biz_dt    secu_code  matched_num
20100901  10001      100
20100902  10001      100
20100903  10001      101
20100904  10001      101
20100905  10001      101
20100906  10001      104
20100901  10002      107
20100902  10002      103
20100903  10002      103
20100904  10002      103
20100905  10002      103
20100906  10002      117没有某日期的数据用上一天的数据来填充

Answer 1

简单的方法就是循环判断，再去插入，当然：也可以用connect by，把缺失的日期查出来，将查出来的结果链接原表，去插入！方法多多.....

Answer 2

update 目标表 a 
set matched_num=(select matched_num from yuan  where (biz_dt,secu_code)=(select max(biz_dt),max(secu_code) from 源表 b where b.secu_code=a.secu_code and b.biz_dt<=a.biz_dt));
commit;
楼主要自己新建你的源表，和目标表，我理解目标表的前两列（biz_dt,secu_code）你也应该是自己插进去吧，然后执行上面的代码，就能得到目标表的第三列matched_num了。

Answer 3

scott@SZTYORA>
scott@SZTYORA> CREATE TABLE tb(
  2    biz_dt varchar2(10),
  3    secu_code number(18,0),
  4    matched_num number(18,0)
  5  );表已创建。scott@SZTYORA>
scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100901', 10001, 100);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100902', 10001, 100);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100903', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100904', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100908', 10001, 101);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100912', 10001, 104);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100913', 10002, 107);已创建 1 行。scott@SZTYORA> INSERT INTO tb(biz_dt, secu_code, matched_num)
  2  VALUES('20100919', 10002, 103);已创建 1 行。scott@SZTYORA>
scott@SZTYORA> select * from tb;BIZ_DT                SECU_CODE MATCHED_NUM
-------------------- ---------- -----------
20100901                  10001         100
20100902                  10001         100
20100903                  10001         101
20100904                  10001         101
20100908                  10001         101
20100912                  10001         104
20100913                  10002         107
20100919                  10002         103已选择8行。scott@SZTYORA> DECLARE
  2    v_cdate VARCHAR2(10);
  3    v_mincdate VARCHAR2(10);
  4    v_maxcdate VARCHAR2(10);
  5    v_cnt NUMBER(18,0);
  6  BEGIN
  7    SELECT min(biz_dt) as min_biz_dt, max(biz_dt) as max_biz_dt into v_mincdate, v_maxcdate FROM tb;
  8    v_cdate := v_mincdate;
  9
 10    v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
 11    WHILE v_mincdate<v_maxcdate LOOP
 12      SELECT COUNT(biz_dt) INTO v_cnt FROM tb WHERE biz_dt=v_mincdate;
 13      IF v_cnt=0 THEN -- 如果没有找到当天的记录，插入当天的记录；
 14        INSERT INTO tb(biz_dt, secu_code, matched_num)
 15        SELECT v_mincdate, secu_code, matched_num FROM tb WHERE biz_dt=v_cdate;
 16        v_cdate := v_mincdate;
 17      ELSE
 18        v_cdate := v_mincdate;
 19      END IF;
 20      v_mincdate := to_char(to_date(v_mincdate,'yyyymmdd')+1,'yyyymmdd');
 21    END LOOP;
 22    COMMIT;
 23  END;
 24  /PL/SQL 过程已成功完成。scott@SZTYORA> select * from tb order by biz_dt;BIZ_DT                SECU_CODE MATCHED_NUM
-------------------- ---------- -----------
20100901                  10001         100
20100902                  10001         100
20100903                  10001         101
20100904                  10001         101
20100905                  10001         101
20100906                  10001         101
20100907                  10001         101
20100908                  10001         101
20100909                  10001         101
20100910                  10001         101
20100911                  10001         101
20100912                  10001         104
20100913                  10002         107
20100914                  10002         107
20100915                  10002         107
20100916                  10002         107
20100917                  10002         107
20100918                  10002         107
20100919                  10002         103已选择19行。

Answer 4

试下这个Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as xxxxx
SQL> 
SQL> with tablea as(
  2  select '20100901' biz_dt,  '10001' secu_code, 100 matched_num from dual union all
  3  select '20100903' biz_dt,  '10001' secu_code, 101 matched_num from dual union all
  4  select '20100906' biz_dt,  '10001' secu_code, 104 matched_num from dual union all
  5  select '20100901' biz_dt,  '10002' secu_code, 107 matched_num from dual union all
  6  select '20100902' biz_dt,  '10002' secu_code, 103 matched_num from dual union all
  7  select '20100906' biz_dt,  '10002' secu_code, 117 matched_num from dual
  8  )
  9  select (to_number(a) + h - 1) biz_dt, b secu_code, c matched_num
 10    from (select m.*, row_number() over(partition by b, rn order by b, a) h
 11            from (select biz_dt a,
 12                         a.secu_code b,
 13                         a.matched_num c,
 14                         lead(biz_dt, 1) over(partition by secu_code order by biz_dt) d,
 15                         rownum rn
 16                    from tableA a) m
 17          connect by rownum - m.rn < m.d - m.a)
 18  /    BIZ_DT SECU_CODE MATCHED_NUM
---------- --------- -----------
  20100901 10001             100
  20100902 10001             100
  20100903 10001             101
  20100904 10001             101
  20100905 10001             101
  20100906 10001             104
  20100901 10002             107
  20100902 10002             103
  20100903 10002             103
  20100904 10002             103
  20100905 10002             103
  20100906 10002             103
  20100906 10002             11713 rows selectedSQL> 

Answer 5

这个有意思... 用PL-SQL过程来做，很简单。如果楼主只是想用一条 select 语句实现，有难度...
如果不能修改原表的数据（复制表或者插入新数据），可以加上辅助表么？

Answer 6

-- 这个还是有问题：  2  select '20100901' biz_dt,  '10001' secu_code, 100 matched_num from dual union all
  3  select '20100903' biz_dt,  '10001' secu_code, 101 matched_num from dual union all
  4  select '20100906' biz_dt,  '10001' secu_code, 104 matched_num from dual union all
  5  select '20100901' biz_dt,  '10002' secu_code, 107 matched_num from dual union all
  6  select '20100902' biz_dt,  '10002' secu_code, 103 matched_num from dual union all
  7  select '20100906' biz_dt,  '10002' secu_code, 117 matched_num from dual-- 看：4号和5号（20100904、20100905)的数据，用3号的数据（3号只有一条数据）去填充的话，
所有的数据应该是8条（现有6条，新补2条）才对！

Answer 7

---------那个是有点问题，用这个，应该是LZ的要求
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as xxxxx
SQL> 
SQL> with tablea as(
  2  select '20100901' biz_dt, '10001' secu_code, 100 matched_num
  3    from dual
  4  union all
  5  select '20100903' biz_dt, '10001' secu_code, 101 matched_num
  6    from dual
  7  union all
  8  select '20100906' biz_dt, '10001' secu_code, 104 matched_num
  9    from dual
 10  union all
 11  select '20100901' biz_dt, '10002' secu_code, 107 matched_num
 12    from dual
 13  union all
 14  select '20100902' biz_dt, '10002' secu_code, 103 matched_num
 15    from dual
 16  union all
 17  select '20100906' biz_dt, '10002' secu_code, 117 matched_num
 18    from dual
 19  union all
 20  select '20100909' biz_dt, '10002' secu_code, 118 matched_num from dual
 21  )
 22  select a biz_dt,b secu_code,c matched_num
 23    from (select distinct a + level - 1 a, b, c
 24            from (select biz_dt a,
 25                         t.secu_code b,
 26                         t.matched_num c,
 27                         nvl(lead(biz_dt, 1)
 28                             over(partition by secu_code order by biz_dt),
 29                             biz_dt) d
 30                    from tableA t)
 31          connect by level < d - a + 1)
 32   order by b, a
 33  /    BIZ_DT SECU_CODE MATCHED_NUM
---------- --------- -----------
  20100901 10001             100
  20100902 10001             100
  20100903 10001             101
  20100904 10001             101
  20100905 10001             101
  20100906 10001             104
  20100901 10002             107
  20100902 10002             103
  20100903 10002             103
  20100904 10002             103
  20100905 10002             103
  20100906 10002             117
  20100907 10002             117
  20100908 10002             117
  20100909 10002             11815 rows selectedSQL> 

Answer 8

SQL> with tb as(
  2  select '20100901' biz_dt,  '10001' secu_code, 100 matched_num from dual union all
  3  select '20100903' biz_dt,  '10001' secu_code, 101 matched_num from dual union all
  4  select '20100906' biz_dt,  '10001' secu_code, 104 matched_num from dual union all
  5  select '20100901' biz_dt,  '10002' secu_code, 107 matched_num from dual union all
  6  select '20100902' biz_dt,  '10002' secu_code, 103 matched_num from dual union all
  7  select '20100906' biz_dt,  '10002' secu_code, 117 matched_num from dual
  8  ),
  9  tb2 as
 10  (select distinct biz_dt+level-1 biz_dt,secu_code,matched_num
 11  from(select biz_dt,secu_code,matched_num,
 12  lead(biz_dt,1,biz_dt) over(partition by secu_code order by secu_code) biz_dt2
 13  from tb)
 14  connect by level<=biz_dt2-biz_dt+1
 15  order by secu_code,biz_dt)
 16  select a.biz_dt+0 biz_dt,a.secu_code secu_code,a.matched_num from tb2 a
 17  where (a.biz_dt,a.secu_code) not in(select b.biz_dt,b.secu_code from tb b )
 18  union all
 19  select a.biz_dt+0,a.secu_code,a.matched_num
 20  from tb a,tb2 b
 21  where a.secu_code=b.secu_code and a.biz_dt=b.biz_dt and a.matched_num=b.matched_num
 22  order by secu_code,biz_dt
 23  /
 
    BIZ_DT SECU_CODE MATCHED_NUM
---------- --------- -----------
  20100901 10001             100
  20100902 10001             100
  20100903 10001             101
  20100904 10001             101
  20100905 10001             101
  20100906 10001             104
  20100901 10002             107
  20100902 10002             103
  20100903 10002             103
  20100904 10002             103
  20100905 10002             103
  20100906 10002             117
 
12 rows selected

Answer 9

with tb as(
select '20100901' biz_dt,  '10001' secu_code, 100 matched_num from dual union all
select '20100903' biz_dt,  '10001' secu_code, 101 matched_num from dual union all
select '20100906' biz_dt,  '10001' secu_code, 104 matched_num from dual union all
select '20100901' biz_dt,  '10002' secu_code, 107 matched_num from dual union all
select '20100902' biz_dt,  '10002' secu_code, 103 matched_num from dual union all
select '20100906' biz_dt,  '10002' secu_code, 117 matched_num from dual
),
tb2 as
(select distinct biz_dt+level-1 biz_dt,secu_code,matched_num
from(select biz_dt,secu_code,matched_num,
lead(biz_dt,1,biz_dt) over(partition by secu_code order by secu_code) biz_dt2
from tb)
connect by level<=biz_dt2-biz_dt+1
order by secu_code,biz_dt)
select a.biz_dt biz_dt,a.secu_code secu_code,a.matched_num from tb2 a 
where (a.biz_dt,a.secu_code) not in(select b.biz_dt,b.secu_code from tb b )
union all
select a.biz_dt+0,a.secu_code,a.matched_num  
from tb a,tb2 b 
where a.secu_code=b.secu_code and a.biz_dt=b.biz_dt and a.matched_num=b.matched_num
order by secu_code,biz_dt

Answer 10

SQL> with tb as(
  2  select '20100901' biz_dt,  '10001' secu_code, 100 matched_num from dual union all
  3  select '20100903' biz_dt,  '10001' secu_code, 101 matched_num from dual union all
  4  select '20100906' biz_dt,  '10001' secu_code, 104 matched_num from dual union all
  5  select '20100901' biz_dt,  '10002' secu_code, 107 matched_num from dual union all
  6  select '20100902' biz_dt,  '10002' secu_code, 103 matched_num from dual union all
  7  select '20100906' biz_dt,  '10002' secu_code, 117 matched_num from dual
  8  ),
  9  tb2 as
 10  (select distinct biz_dt+level-1 biz_dt,secu_code,matched_num
 11  from(select biz_dt,secu_code,matched_num,
 12  lead(biz_dt,1,biz_dt) over(partition by secu_code order by secu_code) biz_dt2
 13  from tb)
 14  connect by level<=biz_dt2-biz_dt+1
 15  order by secu_code,biz_dt)
 16  select a.biz_dt+0 biz_dt,a.secu_code secu_code,a.matched_num from tb2 a
 17  where (a.biz_dt,a.secu_code) not in(select b.biz_dt,b.secu_code from tb b )
 18  union all
 19  select a.biz_dt+0,a.secu_code,a.matched_num
 20  from tb a,tb2 b
 21  where a.secu_code=b.secu_code and a.biz_dt=b.biz_dt and a.matched_num=b.matched_num
 22  order by secu_code,biz_dt
 23  /
 
    BIZ_DT SECU_CODE MATCHED_NUM
---------- --------- -----------
  20100901 10001             100
  20100902 10001             100
  20100903 10001             101
  20100904 10001             101
  20100905 10001             101
  20100906 10001             104
  20100901 10002             107
  20100902 10002             103
  20100903 10002             103
  20100904 10002             103
  20100905 10002             103
  20100906 10002             117
 
12 rows selected

Answer 11

这个的改进还要加个循环create table tbsl as
with tb as(
select '20100901' biz_dt, '10001' secu_code, 100 matched_num from dual union all
select '20100903' biz_dt, '10001' secu_code, 101 matched_num from dual union all
select '20100906' biz_dt, '10001' secu_code, 104 matched_num from dual union all
select '20100901' biz_dt, '10002' secu_code, 107 matched_num from dual union all
select '20100902' biz_dt, '10002' secu_code, 103 matched_num from dual union all
select '20100906' biz_dt, '10002' secu_code, 117 matched_num from dual
)
select * from tbalter table tbsl modify biz_dt varchar2(10)SQL> select * from tbsl
  2  /
 
BIZ_DT     SECU_CODE MATCHED_NUM
---------- --------- -----------
20100901   10001             100
20100903   10001             101
20100906   10001             104
20100901   10002             107
20100902   10002             103
20100906   10002             117
 
6 rows selected
 
SQL> 
SQL> declare
  2  c_date varchar2(10);
  3  c_mindate  varchar2(10);
  4  c_maxdate varchar2(10);
  5  num number;
  6  begin
  7  for i in(select distinct secu_code from tbsl) loop
  8  select max(biz_dt),min(biz_dt) into c_maxdate,c_mindate from tbsl where secu_code=i.secu_code;
  9  c_date:=c_mindate;
 10  c_mindate:=to_char(to_date(c_mindate,'yyyymmdd')+1,'yyyymmdd');
 11  while c_mindate<c_maxdate loop
 12  select count(*) into num from tbsl where secu_code=i.secu_code and biz_dt=c_mindate ;
 13  if num=0 then
 14  insert into tbsl select c_mindate,secu_code,matched_num from tbsl
 15  where biz_dt=c_date and secu_code=i.secu_code;
 16  c_date:=c_mindate;
 17  else
 18  c_date:=c_mindate;
 19  end if;
 20  c_mindate:=to_char(to_date(c_mindate,'yyyymmdd')+1,'yyyymmdd');
 21  end loop;
 22  end loop;
 23  commit;
 24  end;
 25  /
 
PL/SQL procedure successfully completed
 
SQL> select * from tbsl order by secu_code,biz_dt
  2  /
 
BIZ_DT     SECU_CODE MATCHED_NUM
---------- --------- -----------
20100901   10001             100
20100902   10001             100
20100903   10001             101
20100904   10001             101
20100905   10001             101
20100906   10001             104
20100901   10002             107
20100902   10002             103
20100903   10002             103
20100904   10002             103
20100905   10002             103
20100906   10002             117
 
12 rows selected

Answer 12

改进 有点不顺思维
with tb as(
select '20100901' biz_dt, '10001' secu_code, 100 matched_num from dual union all
select '20100903' biz_dt, '10001' secu_code, 101 matched_num from dual union all
select '20100906' biz_dt, '10001' secu_code, 104 matched_num from dual union all
select '20100901' biz_dt, '10002' secu_code, 107 matched_num from dual union all
select '20100902' biz_dt, '10002' secu_code, 103 matched_num from dual union all
select '20100906' biz_dt, '10002' secu_code, 117 matched_num from dual
)
select distinct biz_dt+level-1 biz_dt,secu_code,matched_num
from(select biz_dt,secu_code,matched_num,
lead(biz_dt,1,biz_dt) over(partition by secu_code order by secu_code) biz_dt2
from tb)
connect by level<=biz_dt2-biz_dt
order by secu_code,biz_dt