分组查询取最大时间记录的多种方式

分组查询取最大时间记录的多种方式
    
    sql语句：
    [sql]
    create table dispatch_result  ( 
       dr_id                number                          not null, 
       sheet_id             number, 
       check_next           varchar(20), 
       check_time           date, 
       check_sn             char(8), 
       check_comment        varchar(255), 
       check_status         number, 
       constraint PK_RESULT primary key (dr_id) 
    );    
    
    表记录：
         DR_ID   SHEET_ID CHECK_NEXT           CHECK_TIME  CHECK_SN CHECK_COMMENT     CHECK_STATUS
    ---------- ---------- -------------------- ----------- -------- -------------------------------------------------------------------------------- ------------
             1          1 10001                2012/8/12 11:11:23 10000                                                                           1
             2          1 10002                2012/9/15 11:15:24 10001                                                                           2
             3          2 10001                2012/8/15 9:16:10   10000                                                                           1
             4          2 10002                2012/9/18 11:16:23 10001                                                                           2
查询以SHEET_ID 分组取最大时间记录：
第一种子查询方式：
    [sql]
    select * from dispatch_result dr where dr.check_time=( 
                      select max(check_time) from dispatch_result where sheet_id=dr.sheet_id) 
    
         DR_ID   SHEET_ID CHECK_NEXT           CHECK_TIME  CHECK_SN CHECK_COMMENT     CHECK_STATUS
    ---------- ---------- -------------------- ----------- -------- -------------------------------------------------------------------------------- ------------
             2          1 10002                2012/9/15 11:15:24           10001                                                      2
             4          2 10002                2012/9/18 11:16:23           10001                                   2
     
第二种group by方式:
    [sql]
    select t1.* from dispatch_result t1 right join 
            (select sheet_id,max(check_time) ct from dispatch_result group by sheet_id) t2 
                     on t2.sheet_id=t1.sheet_id and t2.ct=t1.check_time 
    
         DR_ID   SHEET_ID CHECK_NEXT           CHECK_TIME  CHECK_SN CHECK_COMMENT     CHECK_STATUS
    ---------- ---------- -------------------- ----------- -------- -------------------------------------------------------------------------------- ------------
             2          1 10002                2012/9/15 11:15:24           10001                                                      2
             4          2 10002                2012/9/18 11:16:23           10001                                         2
    或
    [sql]
    select * from dispatch_result  
           inner join (select max(dr.check_time) as check_time, dr.sheet_id from dispatch_result dr group by dr.sheet_id) t1  
                 using(check_time,sheet_id); 
      
    CHECK_TIME    SHEET_ID      DR_ID CHECK_NEXT           CHECK_SN CHECK_COMMENT     CHECK_STATUS
    ----------- ---------- ---------- -------------------- -------- -------------------------------------------------------------------------------- ------------
    2012/9/15 11:15:24           1          2 10002                10001                                                               2
    2012/9/18 11:16:23           2          4 10002                10001                                                               2

第三种partition by方式:
    [sql]
    select t2.* from  
           (select t1.*,row_number() over  
                   (partition by t1.sheet_id order by t1.check_time desc nulls last) rn from dispatch_result t1) t2 
                              where rn=1 
     
         DR_ID   SHEET_ID CHECK_NEXT           CHECK_TIME  CHECK_SN CHECK_COMMENT     CHECK_STATUS
    ---------- ---------- -------------------- ----------- -------- -------------------------------------------------------------------------------- ------------
             2          1 10002                2012/9/15 11:15:24           10001                                                      2
             4          2 10002                2012/9/18 11:16:23           10001                                                      2