lag/lead

2018年8月26日 4点热度 0条评论 来源: 上进的菜鸟

lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后;
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)

举例如下:

SQL> select *  from kkk;                                          
                                                                  
        ID NAME                                                   
---------- --------------------                                   
         1 1name                                                  
         2 2name                                                  
         3 3name                                                  
         4 4name                                                  
         5 5name                                                  
                                                                  
SQL> select id,name,lag(name,1,0) over ( order by id )  from kkk; 
                                                                  
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      
---------- -------------------- ----------------------------      
         1 1name                0                                 
         2 2name                1name                             
         3 3name                2name                             
         4 4name                3name                             
         5 5name                4name                             
                                                                  
SQL> select id,name,lead(name,1,0) over ( order by id )  from kkk;
                                                                  
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                2name                             
         2 2name                3name                             
         3 3name                4name                             
         4 4name                5name                             
         5 5name                0                                 
                                                                  
SQL>  
SQL> select id,name,lead(name,2,0) over ( order by id )  from kkk;                                                            
                                                                  
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                3name                             
         2 2name                4name                             
         3 3name                5name                             
         4 4name                0                                 
         5 5name                0                                 
                                                                  
SQL> 
SQL> select id,name,lead(name,1,'alsdfjlasdjfsaf') over ( order by id )  from kkk;                                                             
                                                                                  
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                    
---------- -------------------- ------------------------------                    
         1 1name                2name                                             
         2 2name                3name                                             
         3 3name                4name                                             
         4 4name                5name                                             
         5 5name                alsdfjlasdjfsaf                                   
                                                                                  

    原文作者:上进的菜鸟
    原文地址: https://blog.csdn.net/kylin_learn/article/details/97296528
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。