楼主有点点害人,在5楼把表名改了,列名也改了,数据也不写全.
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join test_table2 n
on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from test_table2 t where t.userId = n.userId)
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join test_table2 n
on m.userId = n.userId and not exists (select 1 from test_table2 t where t.userId = n.userId and t.yearmonth > n.yearmonth)
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join
(
select t.* , row_number() over(partition by userId order by yearmonth desc) px from test_table2 t
) n
on m.userid = n.userid and n.px = 1
create table test_table1(userid number(11) not null, momeny int null );
insert into test_table1 values(1,100);
insert into test_table1 values(2,200);
insert into test_table1 values(3,500);
create table test_table2(id number(11) not null,userid number(11) not null,yearmonth varchar2(6) not null, momeny int null );
insert into test_table2 values(1,1,201108,200);
insert into test_table2 values(2,1,201109,500);
insert into test_table2 values(3,2,201107,700);
insert into test_table2 values(4,2,201109,800);
insert into test_table2 values(5,2,201110,750);
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join test_table2 n
on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from test_table2 t where t.userId = n.userId)
/*
USERID MOMENY YEARMO MOMENY
---------- ---------- ------ ----------
1 100 201109 500
2 200 201110 750
3 500
3 rows selected.
*/
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join test_table2 n
on m.userId = n.userId and not exists (select 1 from test_table2 t where t.userId = n.userId and t.yearmonth > n.yearmonth)
/*
USERID MOMENY YEARMO MOMENY
---------- ---------- ------ ----------
1 100 201109 500
2 200 201110 750
3 500
3 rows selected.
*/
select m.userid , m.momeny , n.yearmonth , n.momeny
from test_table1 m left join
(
select t.* , row_number() over(partition by userId order by yearmonth desc) px from test_table2 t
) n
on m.userid = n.userid and n.px = 1
/*
USERID MOMENY YEARMO MOMENY
---------- ---------- ------ ----------
1 100 201109 500
2 200 201110 750
3 500
3 rows selected.
*/