用SQL把两张表的数据拼成一张表，求救。。。。

[xihuanni0509]

现有这样两张表，数据
table 1
1 1001151000000000JB3S 2012-01 845
2 1001151000000000JB3S 2012-10 1000
3 1001151000000000JB3S 2012-05 1011
table2
1 1001151000000000JB3S 2012 01
2 1001151000000000JB3S 2012 02
3 1001151000000000JB3S 2012 03
4 1001151000000000JB3S 2012 04
5 1001151000000000JB3S 2012 05
6 1001151000000000JB3S 2012 06
7 1001151000000000JB3S 2012 07
8 1001151000000000JB3S 2012 08
9 1001151000000000JB3S 2012 09
10 1001151000000000JB3S 2012 10
11 1001151000000000JB3S 2012 11
12 1001151000000000JB3S 2012 12


我需要得到的数据格式为：
1 1001151000000000JB3S 2012 01 845
2 1001151000000000JB3S 2012 02 845
3 1001151000000000JB3S 2012 03 845
4 1001151000000000JB3S 2012 04 845
5 1001151000000000JB3S 2012 05 1011
6 1001151000000000JB3S 2012 06 1011
7 1001151000000000JB3S 2012 07 1011
8 1001151000000000JB3S 2012 08 1011
9 1001151000000000JB3S 2012 09 1011
10 1001151000000000JB3S 2012 10 1000
11 1001151000000000JB3S 2012 11 1000
12 1001151000000000JB3S 2012 12 1000


各位大虾，这个SQL该怎么写啊，求助。。。。。。


;

create table TABLE1
(
  psncode   CHAR(20) not null,
  yearmonth VARCHAR2(50) not null,
  dvalue    NUMBER
)
;
create table TABLE2
(
  psncode CHAR(20) not null,
  cyear   CHAR(4) not null,
  cperiod CHAR(2) not null
)
insert into TABLE1 (psncode, yearmonth, dvalue)
values ('1001151000000000JB3S', '2012-01', 845);
insert into TABLE1 (psncode, yearmonth, dvalue)
values ('1001151000000000JB3S', '2012-10', 1000);
insert into TABLE1 (psncode, yearmonth, dvalue)
values ('1001151000000000JB3S', '2012-05', 1011);
commit;
prompt 3 records loaded
prompt Loading TABLE2...
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '01');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '02');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '03');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '04');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '05');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '06');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '07');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '08');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '09');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '10');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '11');
insert into TABLE2 (psncode, cyear, cperiod)
values ('1001151000000000JB3S', '2012', '12');
commit;

[MiceRice]

什么数据库？涉及到函数不一样。

[MiceRice]

如果是Oracle，可以试试看这样：

Select t2.*, (
Select num From table1 t1
Where t1.code = t2.code And t1.rq = (
Select MAX(tmp.rq) From table1 tmp
Where TO_CHAR(tmp.rq, 'YYYY') = t2.nf And TO_CHAR(tmp.rq, 'MM') <= t2.yf
)
) as num
From table2 t2

[小丁丐]

Select t2.*,(Select t1.dvalue from Table1 where t1.psncode=t2.psncode) from Table2 t2;你试试！

[hbwhwang]

楼上的都没把题目看懂，就出馊主意 :-)

[zjgsu_cy_ak]

这两张表没有内在的关系，不过可以用ORACLE中merge语句来实现

[hbwhwang]

我靠，难度较大，浪费我N多时间的一个SQL：注意只能在ORACLE下用啊

select k2.psncode,k2.cyear,k2.cperiod,k1.dvalue from
(select tttt.psncode,substr(tttt.bym,1,4) year,substr(tttt.bym,6,2) bmonth,substr(tttt.eym,6,2) emonth,tttt.dvalue dvalue
from
(select ttt.psncode psncode,ttt.bym bym,ttt.eym eym,ttt2.dvalue
from
(select tt.psncode psncode,tt.bym bym,min(tt.eym) eym from
(select t1.psncode psncode,t1.yearmonth bym,t2.yearmonth eym,t1.dvalue dvalue
from table1 t1
left join table1 t2
on t1.psncode=t2.psncode and t1.yearmonth<t2.yearmonth) tt
group by tt.psncode,tt.bym
order by tt.psncode,bym) ttt,table1 ttt2
where ttt.psncode=ttt2.psncode and ttt.bym=ttt2.yearmonth
order by ttt2.psncode,ttt2.yearmonth) tttt) k1,table2 k2
where k1.psncode=k2.psncode and k1.year =k2.cyear and k1.bmonth<=k2.cperiod and (k1.emonth>k2.cperiod or k1.emonth is null)

[hbwhwang]

忘记排序了，在后面加上：

order by k2.psncode,k2.cyear,k2.cperiod

[hbwhwang]

只管出答案，不管效率的啊
这句：
(select t1.psncode psncode,t1.yearmonth bym,t2.yearmonth eym,t1.dvalue dvalue
from table1 t1
left join table1 t2
on t1.psncode=t2.psncode and t1.yearmonth<t2.yearmonth) tt
会把N条记录变成：（N-1)+(N-2)...+1条记录
基本上是N的平方。
如果你的N够大，这个SQL是无法运行的

[catmiw]

好久没搞SQL了

[hbwhwang]

如果是ORACLE，还可以用rownum功能优化一下，这样就没有效率问题了：
select t2.psncode,t2.cyear,t2.cperiod,t1.dvalue
from table2 t2
left join (
select t1.psncode,t1.yearmonth bym,t2.yearmonth eym,t1.dvalue
from (select rownum row_num,t.psncode,t.yearmonth,t.dvalue
from
(select t.psncode,t.yearmonth,t.dvalue
from table1 t
order by t.psncode,t.yearmonth) t) t1
left join (
select rownum row_num,t.psncode,t.yearmonth,t.dvalue
from
(select t.psncode,t.yearmonth,t.dvalue
from table1 t
order by t.psncode,t.yearmonth) t) t2 on t1.psncode=t2.psncode and t1.row_num=(t2.row_num-1)
order by t1.psncode,t1.yearmonth ) t1
on t2.psncode=t1.psncode and t2.cyear||'-'||t2.cperiod>=t1.bym and (t2.cyear||'-'||t2.cperiod<t1.eym or t1.eym is null)
order by t2.psncode,t2.cyear,t2.cperiod