例子：
table1基本信息
userId（唯一）,basemomeny
1 100
2 200
3 500
table2变化的信息
id(自增) userId（table1的外键） yearmonth momeny
1 1 201108 200
2 1 201109 500
3 2 201107 700
4 2 201109 800
5 2 201110 750
假设table1为基本的工资金额，如果工资金额有变动则记录到表table2中，且记录变动的月份。
想获得这样一个结果：
userId basemomeny yearmonth momeny
1 100 201109 500
2 200 201110 750
3 500 空 空
即变化的信息table2，同一个userid取月份最大的那条记录跟table1做个链接，如果一个sql实现呢。
还有取没有变化的userid的话
是直接 select * from table1 where userid not in (select userid from table2) 吧。
------解决方案--------------------
- SQL code
select *from table1 as aleft join table2 as b on a.UserID=b.UserID and not exists(select 1 from table2 where UserID=b.UserID and yearmonth>b.yearmont)
------解决方案--------------------
楼主有点点害人,在5楼把表名改了,列名也改了,数据也不写全.
- SQL code
select m.userid , m.momeny , n.yearmonth , n.momenyfrom test_table1 m left join test_table2 non 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.momenyfrom test_table1 m left join test_table2 non 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.momenyfrom test_table1 m left join( select t.* , row_number() over(partition by userId order by yearmonth desc) px from test_table2 t) non m.userid = n.userid and n.px = 1create 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.momenyfrom test_table1 m left join test_table2 non 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.momenyfrom test_table1 m left join test_table2 non 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.momenyfrom test_table1 m left join( select t.* , row_number() over(partition by userId order by yearmonth desc) px from test_table2 t) non 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.*/