oracle group 分组当中取最小值 有相关要求，请各位帮个忙

Question

例子：
table1基本信息
userId（唯一）,basemomeny
1              100
2              200
3              500table2变化的信息
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) 吧。

Answer 1

select 
*
from table1 as a
left join table2 as b on a.UserID=b.UserID and not exists(select 1 from table2 where UserID=b.UserID and yearmonth>b.yearmont)

Answer 2

改改，少打了一个字母方法1select 
*
from table1 as a
left join table2 as b on a.UserID=b.UserID and not exists(select 1 from table2 where UserID=b.UserID and yearmonth>b.yearmonth)
方法2select 
*
from table1 as a
left join table2 as b on a.UserID=b.UserID and yearmonth= (select MAX(yearmonth) from table2 where UserID=b.UserID )

Answer 3

select m.userid , m.basemomeny , n.yearmonth , n.momeny
from table1 m left join table2 n
on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from table1 t where t.userId = n.userId)select m.userid , m.basemomeny , n.yearmonth , n.momeny
from table1 m left join table2 n
on m.userId = n.userId and not exists (select 1 from table1 t where t.userId = n.userId and t.yearmonth > n.yearmonth)

Answer 4

select m.userid , m.basemomeny , n.yearmonth , n.momeny
from table1 m left join table2 n
on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from table1 t where t.userId = n.userId)select m.userid , m.basemomeny , n.yearmonth , n.momeny
from table1 m left join table2 n
on m.userId = n.userId and not exists (select 1 from table1 t where t.userId = n.userId and t.yearmonth > n.yearmonth)select m.userid , m.basemomeny , n.yearmonth , n.momeny
from table1 m left join
(
  select t.* , row_number() over(partition by userId order by yearmonth desc) px from table2 t
) n
on m.userid = n.userid and n.px = 1

Answer 5

 create table test_table1(userid number(11) not null, momeny varchar2(255) 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(255) not null, momeny varchar2(255) 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);select
*
from test_table1  a
left join test_table2 b on a.UserID=b.UserID and yearmonth= (select MAX(yearmonth) from test_table2 where UserID=b.UserID )ORA-01799: a column may not be outer-joined to a subquery

Answer 6

select
*
from test_table1 a
left join test_table2 b on a.UserID=b.UserID and b.yearmonth= (select MAX(c.yearmonth) from test_table2 as c where c.UserID=b.UserID )加个别名试试

Answer 7

select 
*
from test_table1  a
left join test_table2  b on a.userid=b.userid and not exists(select 1 from test_table2 where userid=b.userid and yearmonth>b.yearmonth)这个可以

Answer 8

改为这样select *
from 
test_table1 a
left join 
(select b.*
from  
(select UserID,MAX(yearmonth) as yearmonth from test_table2  group by UserID)c
inner join test_table2 b on c.UserID=b.UserID and b.yearmonth=c.yearmonth
)  b on a.UserID=b.UserID

Answer 9

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);
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 201109        800
         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 201109        800
         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 201109        800
         3        500                  3 rows selected.
*/

Answer 10

楼主有点点害人,在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 = 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.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.
*/

Answer 11

问题解决了吗，是第几版的Oracle啊，为什么我的按10楼的说法不行呢？SQL> 
SQL> create table test_table1(userid number(11) not null, momeny varchar2(255) null );
 
Table created
SQL> insert into test_table1 values(1,100);
 
1 row inserted
SQL> insert into test_table1 values(2,200);
 
1 row inserted
SQL> insert into test_table1 values(3,500);
 
1 row inserted
SQL> create table test_table2(id number(11) not null,userid number(11) not null,yearmonth varchar2(255) not null, momeny varchar2(255) null );
 
Table created
SQL> insert into test_table2 values(1,1,201108,200);
 
1 row inserted
SQL> insert into test_table2 values(2,1,201109,500);
 
1 row inserted
SQL> insert into test_table2 values(3,2,201107,700);
 
1 row inserted
SQL> insert into test_table2 values(4,2,201109,800);
 
1 row inserted
 
SQL> 
SQL> select m.userid , m.momeny , n.yearmonth , n.momeny
  2  from test_table1 m left join test_table2 n
  3  on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from test_table2 t where t.userId = n.userId)
  4  /
 
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)
 
ORA-01799: 列は副問合せに対して外部結合されません。
 
SQL> 
SQL> select m.userid , m.momeny , n.yearmonth , n.momeny
  2  from test_table1 m left join test_table2 n
  3  on m.userId = n.userId and n.yearmonth = (select max(yearmonth) from test_table2 t where t.userId = n.userId)
  4  ;
 
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)
 
ORA-01799: 列は副問合せに対して外部結合されません。