oracle 用分析函数ROW_NUMBER() OVER(）不用group by，得到最大时间的数据,主键id

港控/mmm° 2021-11-09 23:01 134阅读 0赞

我的项目需求，按DETECTOR\_ID进行分组 找到最大时间的数据。我的思路是分组获取最大时间数据的唯一uuid再自关联查出

原先获取最大时间sql:

SELECT  
MAX(RECORD\_ID)   AS RECORD\_ID       ,  
MAX(RECORD\_DATE) AS  RECORD\_DATE ,  
DETECTOR\_ID  
FROM TRH\_DETECTOR\_RECORD  
GROUP BY DETECTOR\_ID;

但是ID 不是当前最大时间的Id而是最大的Id

现在改成  
SELECT B.RN, B.DETECTOR\_ID, B.RECORD\_ID, B.RECORD\_DATE  
  FROM (SELECT ROW\_NUMBER() OVER(PARTITION BY A.DETECTOR\_ID ORDER BY A.RECORD\_DATE DESC) RN,  
               A.\*  
          FROM TRH\_DETECTOR\_RECORD A) B  
 WHERE B.RN = 1;

## 接下来介绍 ROW\_NUMBER() OVER(）  用法及其定义 ##

语法格式：row\_number() over(partition by 分组列 order by 排序列 desc)

一个很简单的例子

1，先做好准备

create table test1(
           id varchar(4) not null,
           name varchar(10) null,
           age varchar(10) null
    );
    
    select * from test1;
    
    insert into test1(id,name,age) values(1,'a',10);
    insert into test1(id,name,age) values(1,'a2',11);
    insert into test1(id,name,age) values(2,'b',12);
    insert into test1(id,name,age) values(2,'b2',13);
    insert into test1(id,name,age) values(3,'c',14);
    insert into test1(id,name,age) values(3,'c2',15);
    insert into test1(id,name,age) values(4,'d',16);
    insert into test1(id,name,age) values(5,'d2',17);

2，开始使用之~

select t.id,
           t.name,
           t.age,
           row_number() over(partition by t.id order by t.age asc) rn
      from test1 t

结果：

id   name    age    rn

1　　 a 　　 10 　　1  
　　1 　　a2 　  11   　2  
　　2 　　b 　　 12 　　1  
　　2 　　b2 　  13 　　2  
　　3 　　c 　　 14 　　1  
　　3 　　c2 　  15  　 2  
　　4　　 d 　　 16 　　1  
　　5 　　d2 　　17 　　1

3，进一步排序

select * from (select t.id,
           t.name,
           t.age,
           row_number() over(partition by t.id order by t.age asc) rn
      from test1 t) where rn < 2

结果：

id    name   age    rn

1 　　a 　　10 　　1  
　　2 　　b 　　12 　　1  
　　3 　　c 　　14 　　1  
　　4 　　d 　　16 　　1  
　　5 　　d2 　 17 　  1

4，总结

工作半年的经验来看，基本上row\_number() over()这个函数主要用在各种数据统计的sql中，感觉比group by好用的都，可以在一个查询中对多列数据进行分组，尤其在多表关联查询中，row\_number() over()还是非常便捷的。