Oracle over()函数使用说明

作者：袖梨 2022-06-29

例1

 代码如下 复制代码

–select * from tmn_zhbg order by id desc

select id, mc,fs,class,sum (fs) over (order by fs,id) 连续累加,sum (fs) over (order by fs) 连续累加XX,sum (fs) over (partition by id ) 分组求和,
sum (fs) over (partition by id order by fs ) 分组id连续
from
(
select 1 id,’yw’ mc,50 fs,1 class from dual
union all
select 1 , ‘sx’ , 81,1 from dual
union all
select 1 , ‘yy’ , 20,1 from dual
union all
select 2 , ‘yw’ , 81,1 from dual
union all
select 2 , ‘sx’ , 32,1 from dual
union all
select 2 , ‘yy’ , 21,1 from dual
union all
select 4 , ‘yw’ , 10,1 from dual
union all
select 4 , ‘sx’ , 82,1 from dual
union all
select 4 , ‘yy’ , 20,1 from dual
) a

补充一个实例

表t_pi_part 
字段  id  code   name
value 1  222     a
value 2  222     b
value 3 333     c
给code相同的part code 添加行标，根据id 排序
 

 代码如下 复制代码
select p.* ,row_number()over(partition by p.code order order by a.id desc) as row_index from t_pi_part p;

 
 
 
一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。                                       
1：统计某商店的营业额。       
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则：按天统计：每天都统计前面几天的总额
    得到的结果：
    DATE   SALE       SUM
    ----- -------- ------
    1      20        20           --1天          
    2      15        35           --1天＋2天          
    3      14        49           --1天＋2天＋3天          
    4      18        67            .         
    5      30        97            .
    
2:统计各班成绩第一名的同学信息
    NAME   CLASS S                        
    ----- ----- ----------------------
    fda    1      80                    
    ffd    1      78                    
    dss    1      95                    
    cfe    2      74                    
    gds    2      92                    
    gf     3      99                    
    ddd    3      99                    
    adf    3      45                    
    asdf   3      55                    
    3dd    3      78             
  
    通过：  
    --
 

 代码如下 复制代码
   select * from                                                                      
    (                                                                           
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )                                                                           
    where mm=1

    --
    得到结果：
    NAME   CLASS S                       MM                                                                                       
    ----- ----- ---------------------- ----------------------
    dss    1      95                      1                     
    gds    2      92                      1                     
    gf     3      99                      1                     
    ddd    3      99                      1         
  
    注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果         
    2.rank()和dense_rank()的区别是：
      --rank()是跳跃排序，有两个第二名时接下来就是第四名
      --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

相关文章

精彩推荐