条件:
corp money begindate enddate
1001 217 2011-04 2011-08
1002 500 2011-05 2011-07
1003 425 2011-01 2011-01
目标:
corp money dates
1001 217 2011-04
1001 217 2011-05
1001 217 2011-06
1001 217 2011-07
1001 217 2011-08
1002 500 2011-05
1002 500 2011-06
1002 500 2011-07
1003 425 2011-01条件是目前查询出来的结果. 要求通过sql的方法将结果转换成目标的样子.
紧急求教. 跪求各位高手解救.
corp money begindate enddate
1001 217 2011-04 2011-08
1002 500 2011-05 2011-07
1003 425 2011-01 2011-01
目标:
corp money dates
1001 217 2011-04
1001 217 2011-05
1001 217 2011-06
1001 217 2011-07
1001 217 2011-08
1002 500 2011-05
1002 500 2011-06
1002 500 2011-07
1003 425 2011-01条件是目前查询出来的结果. 要求通过sql的方法将结果转换成目标的样子.
紧急求教. 跪求各位高手解救.
解决方案 »
- 刚看看完oracle基础,大家推荐一些oracle优化的书籍吧!
- 多表查询Sql语句
- oracle 导出 ACCESS的语句 急用
- oracle 不确定列的行列转换
- 按指定id顺序输出,例如id为(3,12,8,9,45,6),要求查询结果也按照这个顺序输出。这个id是已知的。请高手指定,多谢!!
- 如何在ASP里通过ado连接ORACLE(例如ORACLE816,ORACLE9I),是不是需要更新ADO组件?如何用ADODB.Stream读出带有返回结果集的ORACLE存储过
- 一个用户向另一个用户导数据
- 问个函数
- 用存储过程如何创建表temp_table???
- 各位大侠,Oracle错误,不知有没有人能解决这个问题
- 如何用SELECT语句实现下面的逻辑?
- Oracle 如何高效查询? 跪求高手
with tab as(
select '1001' corp, 217 money,to_date('2011-04','yyyy-mm') begindate, to_date('2011-08','yyyy-mm') enddate from dual union all
select '1002', 500, to_date('2011-05','yyyy-mm'), to_date('2011-07','yyyy-mm') from dual union all
select '1003', 425, to_date('2011-01','yyyy-mm'), to_date('2011-01','yyyy-mm') from dual
)
select distinct corp,money,To_Char(add_months(begindate,level-1),'yyyy-mm') dates
from (select corp,money,begindate,enddate,months_between(enddate,begindate)+1 months from tab)
connect by level<=months--result:
CORP MONEY DATES
1001 217 2011-04
1001 217 2011-05
1001 217 2011-06
1001 217 2011-07
1001 217 2011-08
1002 500 2011-05
1002 500 2011-06
1002 500 2011-07
1003 425 2011-01
corp number(18,0),
money number(18,2),
begindate varchar2(7),
enddate varchar2(7)
);insert into tb(corp,money,begindate,enddate) values(1001, 217, '2011-04', '2011-08');
insert into tb(corp,money,begindate,enddate) values(1002, 500, '2011-05', '2011-07');
insert into tb(corp,money,begindate,enddate) values(1003, 425, '2011-01', '2011-01');commit;with a as(select min(begindate) as min_begindate, max(enddate) as max_begindate from tb),
b as(select add_months(to_date(min_begindate,'yyyy-mm'),level-1) as dates
from a connect by level <= months_between(to_date(max_begindate,'yyyy-mm'),to_date(min_begindate,'yyyy-mm'))+1)
select tb.corp, tb.money, to_char(b.dates,'yyyy-mm') as dates
from tb join b on to_char(b.dates,'yyyy-mm')>=tb.begindate and to_char(b.dates,'yyyy-mm')<=tb.enddate;
create table t(corp varchar(20),money number,begindate date,enddate date);
insert into t values ('1001',217,to_date('2011-04','yyyy-mm'),to_date('2011-08','yyyy-mm'));
insert into t values ('1002',500,to_date('2011-05','yyyy-mm'),to_date('2011-07','yyyy-mm'));
insert into t values ('1003',425,to_date('2011-01','yyyy-mm'),to_date('2011-01','yyyy-mm'));select t.corp, t.money, add_months(begindate, rn - 1) mon
from t,
(select rownum rn
from (select max(months_between(enddate, begindate) + 1) m from t) a
connect by rownum < m) n
where t.enddate >= add_months(t.begindate, rn - 1)
order by corp, mon1 1001 217 2011-4-1
2 1001 217 2011-5-1
3 1001 217 2011-6-1
4 1001 217 2011-7-1
5 1001 217 2011-8-1
6 1002 500 2011-5-1
7 1002 500 2011-6-1
8 1002 500 2011-7-1
9 1003 425 2011-1-1