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

最新推荐文章于 2023-08-02 00:13:30 发布

Asher

最新推荐文章于 2023-08-02 00:13:30 发布

阅读量6.1k

点赞数 6

分类专栏： PL/SQL 文章标签： join table date null sql

本文链接：https://blog.csdn.net/hz_blog/article/details/7868527

版权

PL/SQL 专栏收录该内容

50 篇文章 0 订阅

订阅专栏

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 分组取最大时间记录：

第一种子查询方式：

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方式:

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

或

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);

再或者

select t1.* from dispatch_result t1 WHERE (t1.sheet_id,t1.check_time) IN 
        (select sheet_id,max(check_time) ct from dispatch_result group by 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方式:

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

结果都是一样的，至于效率求大神指教。