分组查询取最大时间记录的方法Oracle

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)

); www.2cto.com

表记录：

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

www.2cto.com

第二种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);

www.2cto.com

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

www.2cto.com

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