有表T_1(index_1, crop_id, corp_name, start_time, end_time),
需要生成T_2(index_2, corp_id, corp_name, year_time, month_time).
其中index_1和index2由SEQUENCE产生
数据如下:
T_1
index_1, corp_id, corp_name, start_time, end_time
101 1 microsoft 2007-5 2007-7
102 2 ibm 2008-1 2008-2T_2
index_2, corp_id corp_name year_time month_time
8 1 microsoft 2007 5
9 1 microsoft 2007 6
10 1 microsoft 2007 7
11 2 ibm 2008 1
12 2 ibm 2008 2
需要生成T_2(index_2, corp_id, corp_name, year_time, month_time).
其中index_1和index2由SEQUENCE产生
数据如下:
T_1
index_1, corp_id, corp_name, start_time, end_time
101 1 microsoft 2007-5 2007-7
102 2 ibm 2008-1 2008-2T_2
index_2, corp_id corp_name year_time month_time
8 1 microsoft 2007 5
9 1 microsoft 2007 6
10 1 microsoft 2007 7
11 2 ibm 2008 1
12 2 ibm 2008 2
也可以做啊
用START_TIME和END_TIME生成5,6,7,然后再关联,我想一下,去写写试试看
先用游标取出所有纪录,然后每条取开始时间,结束时间,做个循环插入
如果能整成一条SQL的话,那就佩服了...
这个问题主要是select 的sql语句比较难,生成t_2表就不成问题了.
搞了个测试,应该没有问题
create table T_1
(index_1 number, crop_id number, corp_name varchar2(20), start_time date, end_time date);
插入数据:
101 1 microsoft 2007-5-1 2007-7-1
102 2 ibm 2008-1-1 2008-2-1
select distinct crop_id,corp_name,to_char(start_time,'yyyymm'),add_months(start_time,level-0)
from T_1
connect by level <=(months_between(end_time,start_time)+1);
结果1 microsoft 200705 2007-6-1
1 microsoft 200705 2007-7-1
1 microsoft 200705 2007-8-1
2 ibm 200801 2008-2-1
2 ibm 200801 2008-3-1
上面的语句只是提供大家共同学习讨论,不建议使用.
因为connect by 是递归方法,每增加一层就会按指数增长,效率会下降很快.
select distinct crop_id,corp_name,to_char(start_time,'yyyymm'),add_months(start_time,level-1)
from T_1
connect by level <=(months_between(end_time,start_time)+1);
建议用过程来实现,简单通用。
另外一个表格存储
Cmon , cyyyy, cMM
2007-02 2007 02
2007-03 2007 03估计你的需求,不会有几千几万年吧.就是几千,也最多就1万来条记录.
select seq.nextval,crop_id, corp_name,to_char(start_time,'YYYY') as year_time,to_char(start_time,'MM') as month_time from T_1
union
select seq.nextval,crop_id, corp_name,to_char(end_time,'YYYY') as year_time,to_char(end_time,'MM') as month_time from T_1
create table T_1
(
corp_id int
,corp_name varchar(10)
,start_time date
,end_time date
)--创建T_2
create table T_2
(
corp_id int
,corp_name varchar(10)
,year_time DATE
,month_time date
)--插入数据
insert into T_1 values('1','microsoft',to_date('2007-05','yyyy-mm'),to_date('2007-07','yyyy-mm'))
insert into T_1 values('2','ibm',to_date('2008-01','yyyy-mm'),to_date('2008-02','yyyy-mm'))
--创建存储过程
create or replace procedure pro_test_A is
i INT;
j INT;
A01 INT;
A02 varchar2(10);
A03 Date;
A04 Date;
A05 INT;
A06 INT;
tmpA INT;
tmpB INT;
CURSOR cur_q IS SELECT * FROM t_1;
BEGIN
OPEN cur_q;
LOOP
FETCH cur_q INTO A01,A02,A03,A04;
EXIT WHEN cur_q%NOTFOUND;
tmpA :=MONTH(A03);
tmpB :=MONTH(A04);
A06 :=YEAR(A03);
A05 := tmpB-tmpA;
j :=tmpA-1;
FOR i in 0..A05 Loop
j :=j+1;
INSERT INTO T_2 values(A01,A02,A06,j);
COMMIT;
END Loop;
END LOOP;
CLOSE Cur_q;
END; ______________________
SQL: exec pro_test_a
______________________
RESULT:
1 2 ibm 2008 1
2 2 ibm 2008 2
3 1 microsoft 2007 5
4 1 microsoft 2007 6
5 1 microsoft 2007 7
select crop_id,
corp_name,
to_char(add_months(start_time, rn - 1),'yyyymm')
from T_1 a,
(select level rn
from (select max(months_between(end_time, start_time) + 1) mrn
from t_1)
connect by level <= mrn) b
where (months_between(end_time, start_time) +1) >= b.rn;--输出的结果就是
1 microsoft 200706
1 microsoft 200707
1 microsoft 200705
2 ibm 200802
2 ibm 200801