ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法

今天药忘吃喽~ 2022-12-27 02:25 176阅读 0赞

ORACLE 中的 ROW\_NUMBER() OVER() 分析函数的用法

ROW\_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

举例：

SQL> DESC T1;

Name                                           Null?    Type  
\----------------------------------------- -------- ----------------------------  
ID                                                    NUMBER  
NAME                                                    VARCHAR2(10)  
DATE1                                                    DATE  
  
SQL> SELECT \* FROM T1;  
        ID NAME                           DATE1  
\---------- ------------------------------ ------------------  
       101 aaa                                  09-SEP-13

101 bbb                                  10-SEP-13

101 ccc                                  11-SEP-13

102 ddd                                  08-SEP-13

102 eee                                  11-SEP-13

SQL> SELECT ID,NAME,DATE1,ROW\_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1;  
  
    ID NAME               DATE1               RN  
\---------- ------------------------------ ------------------ ----------  
       101 ccc                  11-SEP-13              1  
       101 bbb                  10-SEP-13              2  
       101 aaa                  09-SEP-13              3  
       102 eee                  11-SEP-13              1  
       102 ddd                  08-SEP-13              2

把上面语句作为一个子表语句，嵌入到另一条语句中：

SQL> SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW\_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1)T WHERE T.RN=1;  
  
        ID NAME                           DATE1  
\---------- ------------------------------ ------------------  
       101 ccc                                  11-SEP-13  
       102 eee                                  11-SEP-13

转载地址：[https://blog.csdn.net/iw1210/article/details/11937085?utm\_medium=distribute.pc\_relevant\_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth\_1-utm\_source=distribute.pc\_relevant\_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control][https_blog.csdn.net_iw1210_article_details_11937085_utm_medium_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control_depth_1-utm_source_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control]

[https_blog.csdn.net_iw1210_article_details_11937085_utm_medium_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control_depth_1-utm_source_distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control]: https://blog.csdn.net/iw1210/article/details/11937085?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control