关于ＳＱＬ语句的select语句

zxmzhf

1. select a.USER_ID, a.USER_STATUS ,decode(nvl(b.USER_ID,'@'),'@',0, 1),nvl(b.business_type ,0) ,nvl(b.PERIOD_ID,'@') from TAB_CH_USER a, TAB_FEE_RENT_RECORD b   where a.USER_ID=b.USER_ID(+) and a.USER_STATUS=1

复制代码

在上面的SQL语句中，符合条件a.USER_ID=b.USER_ID的b表中可能有多条记录，现在我想如果b中有多条的话，则只返回b表的b.PERIOD_ID最大的那条记录，请问大家条件该怎么控制呢？[/code]

ruknow

1. select a.USER_ID, a.USER_STATUS ,decode(nvl(b.USER_ID,'@'),'@',0, 1),nvl(b.business_type ,0) ,nvl(b.PERIOD_ID,'@')
   
2. from TAB_CH_USER a,
   
3. (select * from TAB_FEE_RENT_RECORD where PERIOD_ID in
   
4. (select max(PERIOD_ID) from TAB_FEE_RENT_RECORD group by USER_ID)) b   
   
5. where a.USER_ID=b.USER_ID(+) and a.USER_STATUS=1

复制代码

zxmzhf

非常感谢楼上的ruknow!

ruknow

[quote]原帖由 "zxmzhf"]非常感谢楼上的ruknow![/quote 发表：