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

[xu_xue_lin]

例子：
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) 吧。

[中国风]

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)

[中国风]

[Quote=引用 1 楼 roy_88 的回复:]

SQL code

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)
[/Quote]

改改，少打了一个字母

方法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.yearmonth)


方法2

select 
*
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 )

[dawugui]

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)

[dawugui]

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

[xu_xue_lin]

[Quote=引用 2 楼 roy_88 的回复:]

引用 1 楼 roy_88 的回复:

SQL code

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)


改改，少……
[/Quote]
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

[中国风]

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 )

加个别名试试

[中国风]

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)

这个可以

[中国风]

[Quote=引用 6 楼 roy_88 的回复:]

SQL code

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 )


加个别名试试
[/Quote]

改为这样

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

[dawugui]

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.
*/

[dawugui]

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

[xu_xue_lin]

谢谢各位！

[SilverMoonlight]

问题解决了吗，是第几版的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: 列は副問合せに対して外部結合されません。