下边这种场所只适用id 和name是各类对应的,不然查询出来的数据是不得法的。

Oracle的LAG和LEAD剖判函数

 

 

 

Oracle解析函数ROW_NUMBEPAJERO()|RANK()|LAG()使用详解

 

 

1.3、帮忙精晓over()的实例

例1:关心点:sql无排序,over()排序子句轻易

SELECT DEPTNO, EMPNO, ENAME, SAL, 
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

运转结果:

 

金沙糖果派对网站app 1

        

例2:关心点:sql无排序,over()排序子句有,窗口省略

 

金沙糖果派对网站app 2

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO 
                            ORDER BY SAL DESC)
  FROM EMP;

金沙糖果派对网站app 3

运营结果:

 

金沙糖果派对网站app 4

                   
例3:关心点:sql无排序,over()排序子句有,窗口也可能有,窗口特意重申全组数据

 

金沙糖果派对网站app 5

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) 
       OVER(PARTITION BY DEPTNO 
            ORDER BY SAL 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP;

金沙糖果派对网站app 6

运转结果:

 

金沙糖果派对网站app 7

      
例4:关怀点:sql有排序(正序),over()排序子句无,先做sql排序再开展辨析函数运算

 

金沙糖果派对网站app 8

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR;

金沙糖果派对网站app 9

运维结果:

 

金沙糖果派对网站app 10

 

例5:关切点:sql有排序(倒序),over()排序子句无,先做sql排序再拓展分析函数运算

 

金沙糖果派对网站app 11

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 12

运维结果:

金沙糖果派对网站app 13

                 

例6:关心点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据只是不排序,而后排序子句先排序并实行深入分析函数管理(窗口暗中同意为首先行到方今行),最终再开展sql排序

 

 

金沙糖果派对网站app 14

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 15

运营结果:

金沙糖果派对网站app 16

 

金沙糖果派对网站app 17

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 18

运营结果:

金沙糖果派对网站app 19

              

 

深入分析:每一个人学号一定是例外的,名字大概有重名,最大复杂的状态是,每一个班最高成绩大概持续三个。

Oracle分析函数ROW_NUMBELX570()|RANK()|LAG()使用详解

 

1.3、协理了然over()的实例

例1:关切点:sql无排序,over()排序子句简单

SELECT DEPTNO, EMPNO, ENAME, SAL, 
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

运营结果:

 

金沙糖果派对网站app 20

        

例2:关怀点:sql无排序,over()排序子句有,窗口省略

 

金沙糖果派对网站app 21

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO 
                            ORDER BY SAL DESC)
  FROM EMP;

金沙糖果派对网站app 22

运作结果:

 

金沙糖果派对网站app 23

                   
例3:关怀点:sql无排序,over()排序子句有,窗口也可以有,窗口特意强调全组数据

 

金沙糖果派对网站app 24

SELECT DEPTNO,
       EMPNO,
       ENAME,
       SAL,
       LAST_VALUE(SAL) 
       OVER(PARTITION BY DEPTNO 
            ORDER BY SAL 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP;

金沙糖果派对网站app 25

运作结果:

 

金沙糖果派对网站app 26

      
例4:关心点:sql有排序(正序),over()排序子句无,先做sql排序再开始展览辨析函数运算

 

金沙糖果派对网站app 27

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR;

金沙糖果派对网站app 28

运作结果:

 

金沙糖果派对网站app 29

 

例5:关切点:sql有排序(倒序),over()排序子句无,先做sql排序再拓展解析函数运算

 

金沙糖果派对网站app 30

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 31

运作结果:

金沙糖果派对网站app 32

                 

例6:关切点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据只是不排序,而后排序子句先排序并进行剖析函数处理(窗口默以为首先行到近日行),最终再开始展览sql排序

 

 

金沙糖果派对网站app 33

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 34

运营结果:

金沙糖果派对网站app 35

 

金沙糖果派对网站app 36

SELECT DEPTNO,
       MGR,
       ENAME,
       SAL,
       HIREDATE,
       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE
  FROM EMP
 WHERE DEPTNO = 30
 ORDER BY DEPTNO, MGR DESC;

金沙糖果派对网站app 37

运作结果:

金沙糖果派对网站app 38

              

 

二、理解over()函数

1.1、多少个order by的进行时机
分析函数(以及与其合作的开窗函数over())是在全体sql查询停止后(sql语句中的order
by的施行相比新鲜)再张开的操作, 也正是说sql语句中的order
by也会潜濡默化分析函数的进行结果:

a) 两个一致:若是sql语句中的order
by知足与剖判函数同盟的开窗函数over()分析时讲求的排序,即sql语句中的order
by子句里的剧情和开窗函数over()中的order by子句里的原委一致,

那就是说sql语句中的排序将西子行,剖判函数在条分缕析时就不要再排序;
b) 两个不雷同:假诺sql语句中的order
by不知足与分析函数同盟的开窗函数over()深入分析时须要的排序,即sql语句中的order
by子句里的从头到尾的经过和开窗函数over()中的order by子句里的内容不等同,

那正是说sql语句中的排序将最终在条分缕析函数深入分析甘休后施行排序。

           

1.2、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数带有多少个深入分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口正是解析函数深入分析时要拍卖的多少范围,就拿sum来讲,它是sum窗口中的记录并非总体分组中的记录,由此大家在想得到有些栏位的累计值时,大家要求把窗口钦命到该分组中的第一行数据到当前行,
假若你钦命该窗口从该分组中的第一行到结尾一行,那么该组中的每二个sum值都会一直以来,即全体组的总和。

      窗口子句在这里本身只说rows情势的窗口,range格局和滑动窗口也不提。

 

     
窗口子句中我们平常利用钦点第一行,当前行,最终一行如此的四个性子:
第一行是 unbounded preceding,
近日行是 current row,
终极一行是 unbounded following,

注释:

开窗函数over()出现分组(partition by)子句时,

unbounded
preceding即首先行是指表中多个分组里的首先行, unbounded
following即最终一行是指表中四个分组里的尾声一行;

开窗函数over()简轻便单了分组(partition by)子句时, 

unbounded
preceding即首先行是指表中的首先行, unbounded
following即最后一行是指表中的最终一行。

 

窗口子句不能单独出现,必须有order by子句时才能出现

例如:

last_value(sal) over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following)

上述示例钦赐窗口为一切分组。而产出order
by子句的时候,不自然要有窗口子句,但职能会很区别样,此时的窗口暗中认可是眼下组的第一行到近来行!

 

假使轻松分组,则把任何笔录当成一个组。
a) 如果存在order by则默认窗口是unbounded preceding and current
row   –当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded
following  –整个组
 

 

而不论是或不是省略分组子句,如下结论都以创造的:

1、窗口子句不能够独立出现,必须有order by子句时手艺冒出

2、当省略窗口子句时:
a) 如若存在order by则暗中同意的窗口是unbounded preceding and current
row  –当前组的首先行到当前行,即在此时此刻组中,第一行到当下行
b) 要是同期省略order by则暗许的窗口是unbounded preceding and unbounded
following  –整个组

              
所以,

 

lag(sal) over(order by sal) 解释

over(order by salary)表暗暗提示义如下:

先是,大家要领悟是因为省略分组子句,所以当前组的限定为一切表的多少行,

下一场,在时下组(此时为任何表的数据行)那个范围里施行排序(即order by
salary),

聊起底,大家清楚解析函数lag(sal)在当下组(此时为一体表的数据行)那一个范围里的窗口范围为方今组的第一行到日前行,即深入分析函数lag(sal)在那几个窗口范围实行。

 

参见:

 

        倘若持续选用起来的点子,那么是无法满意必要的。

二、理解over()函数

1.1、多少个order by的推行机缘
剖判函数(以及与其合作的开窗函数over())是在总体sql查询停止后(sql语句中的order
by的实行比较非常)再拓展的操作, 也正是说sql语句中的order
by也会影响深入分析函数的试行结果:

a) 两个一致:假诺sql语句中的order
by满意与拆解分析函数合作的开窗函数over()深入分析时须要的排序,即sql语句中的order
by子句里的内容和开窗函数over()中的order by子句里的剧情一律,

那正是说sql语句中的排序将先举行,深入分析函数在深入分析时就不必再排序;
b) 两个分裂等:要是sql语句中的order
by不满意与解析函数同盟的开窗函数over()分析时须要的排序,即sql语句中的order
by子句里的内容和开窗函数over()中的order by子句里的故事情节不同,

那正是说sql语句中的排序将最终在剖判函数深入分析甘休后施行排序。

           

1.2、开窗函数over()分析函数中的分组/排序/窗口
      开窗函数over()分析函数包罗三个分析子句:分组子句(partition
by), 排序子句(order by), 窗口子句(rows)
      窗口便是深入分析函数剖判时要管理的多少范围,就拿sum来讲,它是sum窗口中的记录并非全数分组中的记录,由此我们在想赢得某些栏位的累计值时,大家必要把窗口钦点到该分组中的第一行数据到日前行,
假诺你钦点该窗口从该分组中的第一行到终极一行,那么该组中的每二个sum值都团体首领久以来,即一切组的总和。

      窗口子句在此地笔者只说rows方式的窗口,range方式和滑动窗口也不提。

 

     
窗口子句中咱们平时利用钦点第一行,当前行,最终一行如此的三个特性:
第一行是 unbounded preceding,
时下行是 current row,
谈到底一行是 unbounded following,

注释:

开窗函数over()出现分组(partition by)子句时,

unbounded
preceding即首先行是指表中三个分组里的率先行, unbounded
following即最后一行是指表中贰个分组里的末段一行;

开窗函数over()粗略了分组(partition by)子句时, 

unbounded
preceding即首先行是指表中的率先行, unbounded
following即最后一行是指表中的末尾一行。

 

窗口子句不能单独出现,必须有order by子句时才能出现

例如:

last_value(sal) over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following)

以上示例内定窗口为总体分组。而产出order
by子句的时候,不自然要有窗口子句,但效果与利益会很不平等,此时的窗口私下认可是当前组的首先行到当下行!

 

只要简单分组,则把全部笔录当成五个组。
a) 如果存在order by则默认窗口是unbounded preceding and current
row   –当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded
following  –整个组
 

 

而任由是或不是省略分组子句,如下结论都是起家的:

1、窗口子句不能够独立出现,必须有order by子句时手艺出现

2、当省略窗口子句时:
a) 要是存在order by则暗中同意的窗口是unbounded preceding and current
row  –当前组的第一行到日前行,即在眼下组中,第一行到日前行
b) 假若还要省略order by则暗许的窗口是unbounded preceding and unbounded
following  –整个组

              
所以,

 

lag(sal) over(order by sal) 解释

over(order by salary)表暗暗提示义如下:

第一,我们要精晓是因为省略分组子句,所以当前组的范围为一体表的数额行,

然后,在现阶段组(此时为全方位表的数据行)这些限制里进行排序(即order by
salary),

终极,大家精晓深入分析函数lag(sal)在时下组(此时为总身体表面的数据行)那几个限制里的窗口范围为当下组的率先行到当下行,即剖判函数lag(sal)在这一个窗口范围实行。

 

参见:

 

三、常见剖析函数详解

为了方便开始展览实施,特将演示表和数码罗列如下:

一、创建表

create table t( 
   bill_month varchar2(12) , 
   area_code number, 
   net_type varchar(2), 
   local_fare number 
);

      

二、插入数据

金沙糖果派对网站app 39

insert into t values('200405',5761,'G', 7393344.04); 
insert into t values('200405',5761,'J', 5667089.85); 
insert into t values('200405',5762,'G', 6315075.96); 
insert into t values('200405',5762,'J', 6328716.15); 
insert into t values('200405',5763,'G', 8861742.59); 
insert into t values('200405',5763,'J', 7788036.32); 
insert into t values('200405',5764,'G', 6028670.45); 
insert into t values('200405',5764,'J', 6459121.49); 
insert into t values('200405',5765,'G', 13156065.77); 
insert into t values('200405',5765,'J', 11901671.70); 
insert into t values('200406',5761,'G', 7614587.96); 
insert into t values('200406',5761,'J', 5704343.05); 
insert into t values('200406',5762,'G', 6556992.60); 
insert into t values('200406',5762,'J', 6238068.05); 
insert into t values('200406',5763,'G', 9130055.46); 
insert into t values('200406',5763,'J', 7990460.25); 
insert into t values('200406',5764,'G', 6387706.01); 
insert into t values('200406',5764,'J', 6907481.66); 
insert into t values('200406',5765,'G', 13562968.81); 
insert into t values('200406',5765,'J', 12495492.50); 
insert into t values('200407',5761,'G', 7987050.65); 
insert into t values('200407',5761,'J', 5723215.28); 
insert into t values('200407',5762,'G', 6833096.68); 
insert into t values('200407',5762,'J', 6391201.44); 
insert into t values('200407',5763,'G', 9410815.91); 
insert into t values('200407',5763,'J', 8076677.41); 
insert into t values('200407',5764,'G', 6456433.23); 
insert into t values('200407',5764,'J', 6987660.53); 
insert into t values('200407',5765,'G', 14000101.20); 
insert into t values('200407',5765,'J', 12301780.20); 
insert into t values('200408',5761,'G', 8085170.84); 
insert into t values('200408',5761,'J', 6050611.37); 
insert into t values('200408',5762,'G', 6854584.22); 
insert into t values('200408',5762,'J', 6521884.50); 
insert into t values('200408',5763,'G', 9468707.65); 
insert into t values('200408',5763,'J', 8460049.43); 
insert into t values('200408',5764,'G', 6587559.23); 
insert into t values('200408',5764,'J', 7342135.86); 
insert into t values('200408',5765,'G', 14450586.63); 
insert into t values('200408',5765,'J', 12680052.38); 
commit;

金沙糖果派对网站app 40

            

三、first_value()与last_value():求最值对应的其余属性
难题、抽取每月话费最高和最低的两个地面。

金沙糖果派对网站app 41

SELECT BILL_MONTH, 
       AREA_CODE,
       SUM(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 
  FROM T 
 GROUP BY BILL_MONTH, AREA_CODE 
 ORDER BY BILL_MONTH

金沙糖果派对网站app 42

运作结果:

金沙糖果派对网站app 43

   

四、rank(),dense_rank()与row_number():求排序

rank,dense_rank,row_number函数为每条记下产生二个从1始发至n的自然数,n的值大概低于等于记录的总和。这3个函数的唯一差异在于当遭遇同样数量时的排行战术。
①row_number: 
row_number函数再次来到一个独一的值,当遭受一样数量时,排行依据记录聚焦记录的逐个依次递增。
②dense_rank: 
dense_rank函数重回叁个唯一的值,当碰着一样数量时,此时具备同一数量的排行都以同等的。
③rank: 
rank函数再次回到三个独一的值,当蒙受同样的多少时,此时有着同一数量的排行是均等的,同时会在结尾一条一样记录和下一条不一样记录的排行之间空出排行。

          

亲自去做数据在Oracle自带的scott用户下:
1、rank()值同样有的时候间排行一样,其后排行跳跃不再三再四

金沙糖果派对网站app 44

SELECT * 
  FROM (SELECT DEPTNO, 
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 45

运转结果:

金沙糖果派对网站app 46
2、dense_rank()值相同一时候排行一样,其后排行接二连三不跳跃

金沙糖果派对网站app 47

SELECT * 
  FROM (SELECT DEPTNO, 
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 48

运作结果:

金沙糖果派对网站app 49
3、row_number()值同样一时间排行不等于,其后排行接二连三不跳跃

金沙糖果派对网站app 50

SELECT * 
  FROM (SELECT DEPTNO, 
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 51

运维结果:

金沙糖果派对网站app 52

 

五、lag()与lead():求以前或现在的第N行 
lag和lead函数能够在三次查询中抽取同一字段的前n行的多寡和后n行的值。这种操作可以运用对同样表的表连接来完结,然而使用lag和lead有越来越高的功用。
lag(arg1,arg2,arg3)
首先个参数是列名,
第一个参数是偏移的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> 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                             
金沙糖果派对网站app,         3 3name                5name                             
         4 4name                0                                 
         5 5name                0  
SQL> select id,name,lead(name,1,’linjiqin’) 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               
linjiqin  


   

六、rollup()与cube():排列组合分组 
1)、group by rollup(a, b, c):
先是会对(a、b、c)进行group by,
接下来再对(a、b)举行group by,
后来再对(a)实行group by,
最后对全表进行汇总操作。

     

2)、group by cube(a, b, c):
则第一会对(a、b、c)进行group by,
下一场依次是(a、b),(a、c),(a),(b、c),(b),(c),
最终对全表实行汇总操作。

   

1、生成演示数据:
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 
Connected as ds_trade
 
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 
Connected as SYS
 
SQL> create table scott.t as select * from dba_indexes;
 
Table created
 
 
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 
Connected as scott
 
SQL>

    

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by owner, index_type, status;

金沙糖果派对网站app 53

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C):
率先会对(A、B、C)进行GROUP BY,
然后再对(A、B)进行GROUP BY,
之后再对(A)进行GROUP BY,
最终对全表进行汇总操作。
sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by ROLLUP(owner, index_type, status);

金沙糖果派对网站app 54

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C):
则第一会对(A、B、C)实行GROUP BY,
接下来逐个是(A、B),(A、C),(A),(B、C),(B),(C),
终极对全表举行汇总操作。

sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by cube(owner, index_type, status);

金沙糖果派对网站app 55

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
主题素材:计算出种种位置接连半年的通话成本的平平均数量(移动平均值)

 

金沙糖果派对网站app 56

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum", 
       AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg", 
       MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max", 
       MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH)

金沙糖果派对网站app 57

运维结果:

金沙糖果派对网站app 58

  

难题:求各州段按月度拉长的通话费

金沙糖果派对网站app 59

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

金沙糖果派对网站app 60

运作结果:

金沙糖果派对网站app 61

常用解析函数:(最常用的应该是1.2.3 的排序)
1、row_number() over(partition by … order by …)
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …)
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 可以获取结果聚焦,按自然排序所排列的当下行的上下相邻若干offset
的有个别行的有些列(不用结果集的自关系);
lag ,lead 分别是无边无际,向后;
lag 和lead
有八个参数,第二个参数是列名,第二个参数是偏移的offset,第八个参数是
赶过记录窗口时的暗中同意值)

一、Oracle解析函数入门

 

深入分析函数是怎么着?
分析函数是Oracle特地用来减轻复杂报表计算须求的作用庞大的函数,它能够在数码中张开分组然后总计基于组的某种总结值,并且每一组的每一行都得以回到三个计算值。

          

浅析函数和聚合函数的不一致之处是怎么?
常常的聚合函数用group
by分组,每种分组重回贰个总结值,而分析函数采取partition
by分组,并且每组每行都能够重临贰个计算值。

              

浅析函数的款型
解析函数带有贰个开窗函数over(),富含八个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,
她俩的选用格局如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在此地小编只说rows格局的窗口,range格局和滑动窗口也不提

    

浅析函数例子(在scott用户下模拟)

身体力行指标:彰显各机构职工的工薪,并顺便突显该片段的万丈薪资。

金沙糖果派对网站app 62

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

金沙糖果派对网站app 63

运维结果:

金沙糖果派对网站app 64

               

亲自去做指标:遵照deptno分组,然后总结每组值的总额

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运维结果:

金沙糖果派对网站app 65

     

演示指标:对各机关进行分组,并顺便展现第一行至当前行的集聚

金沙糖果派对网站app 66

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 67

运行结果:

金沙糖果派对网站app 68

   

演示指标:当前行至最终一行的聚集

金沙糖果派对网站app 69

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 70

运作结果:

金沙糖果派对网站app 71

   

 示例指标:当前行的上一行(rownum-1)到当下行的汇总

金沙糖果派对网站app 72

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 73

运转结果:

金沙糖果派对网站app 74

    

身体力行目的:   当前行的上一行(rownum-1)到日前行的下辆行(rownum+2)的汇总     

金沙糖果派对网站app 75

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 76

运维结果:

金沙糖果派对网站app 77

      

 

 

Oracle的LAG和LEAD剖析函数

 

 

 

           2 张三 90

三、常见解析函数详解

为了便于举办实践,特将演示表和数量罗列如下:

一、创建表

create table t( 
   bill_month varchar2(12) , 
   area_code number, 
   net_type varchar(2), 
   local_fare number 
);

      

二、插入数据

金沙糖果派对网站app 78

insert into t values('200405',5761,'G', 7393344.04); 
insert into t values('200405',5761,'J', 5667089.85); 
insert into t values('200405',5762,'G', 6315075.96); 
insert into t values('200405',5762,'J', 6328716.15); 
insert into t values('200405',5763,'G', 8861742.59); 
insert into t values('200405',5763,'J', 7788036.32); 
insert into t values('200405',5764,'G', 6028670.45); 
insert into t values('200405',5764,'J', 6459121.49); 
insert into t values('200405',5765,'G', 13156065.77); 
insert into t values('200405',5765,'J', 11901671.70); 
insert into t values('200406',5761,'G', 7614587.96); 
insert into t values('200406',5761,'J', 5704343.05); 
insert into t values('200406',5762,'G', 6556992.60); 
insert into t values('200406',5762,'J', 6238068.05); 
insert into t values('200406',5763,'G', 9130055.46); 
insert into t values('200406',5763,'J', 7990460.25); 
insert into t values('200406',5764,'G', 6387706.01); 
insert into t values('200406',5764,'J', 6907481.66); 
insert into t values('200406',5765,'G', 13562968.81); 
insert into t values('200406',5765,'J', 12495492.50); 
insert into t values('200407',5761,'G', 7987050.65); 
insert into t values('200407',5761,'J', 5723215.28); 
insert into t values('200407',5762,'G', 6833096.68); 
insert into t values('200407',5762,'J', 6391201.44); 
insert into t values('200407',5763,'G', 9410815.91); 
insert into t values('200407',5763,'J', 8076677.41); 
insert into t values('200407',5764,'G', 6456433.23); 
insert into t values('200407',5764,'J', 6987660.53); 
insert into t values('200407',5765,'G', 14000101.20); 
insert into t values('200407',5765,'J', 12301780.20); 
insert into t values('200408',5761,'G', 8085170.84); 
insert into t values('200408',5761,'J', 6050611.37); 
insert into t values('200408',5762,'G', 6854584.22); 
insert into t values('200408',5762,'J', 6521884.50); 
insert into t values('200408',5763,'G', 9468707.65); 
insert into t values('200408',5763,'J', 8460049.43); 
insert into t values('200408',5764,'G', 6587559.23); 
insert into t values('200408',5764,'J', 7342135.86); 
insert into t values('200408',5765,'G', 14450586.63); 
insert into t values('200408',5765,'J', 12680052.38); 
commit;

金沙糖果派对网站app 79

            

三、first_value()与last_value():求最值对应的其余品质
主题素材、抽取每月话费最高和压低的八个地面。

金沙糖果派对网站app 80

SELECT BILL_MONTH, 
       AREA_CODE,
       SUM(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 
  FROM T 
 GROUP BY BILL_MONTH, AREA_CODE 
 ORDER BY BILL_MONTH

金沙糖果派对网站app 81

运行结果:

金沙糖果派对网站app 82

   

四、rank(),dense_rank()与row_number():求排序

rank,dense_rank,row_number函数为每条记下爆发二个从1方始至n的自然数,n的值恐怕低于等于记录的总和。那3个函数的独一差异在于当碰到一样数量时的排行攻略。
①row_number: 
row_number函数重临一个独一的值,当蒙受一样数量时,排名依据记录集中记录的依次依次递增。
②dense_rank: 
dense_rank函数重回贰个独一的值,当遭遇一样数量时,此时颇具同一数量的排名都是一致的。
③rank: 
rank函数返回二个唯一的值,当遭受同样的数目时,此时具有同一数量的排行是均等的,同有的时候候会在结尾一条一样记录和下一条分化记录的排行之间空出排名。

          

亲自去做数据在Oracle自带的scott用户下:
1、rank()值一样偶然间排行一样,其后名次跳跃不总是

金沙糖果派对网站app 83

SELECT * 
  FROM (SELECT DEPTNO, 
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 84

运营结果:

金沙糖果派对网站app 85
2、dense_rank()值一样一时候排行同样,其后排名三回九转不跳跃

金沙糖果派对网站app 86

SELECT * 
  FROM (SELECT DEPTNO, 
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 87

运作结果:

金沙糖果派对网站app 88
3、row_number()值相同期排行不等于,其后排名延续不跳跃

金沙糖果派对网站app 89

SELECT * 
  FROM (SELECT DEPTNO, 
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

金沙糖果派对网站app 90

运转结果:

金沙糖果派对网站app 91

 

五、lag()与lead():求此前或现在的第N行 
lag和lead函数能够在一回查询中抽出同一字段的前n行的数码和后n行的值。这种操作能够采纳对一样表的表连接来实现,不过使用lag和lead有更加高的效用。
lag(arg1,arg2,arg3)
先是个参数是列名,
其次个参数是偏移的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> 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> select id,name,lead(name,1,’linjiqin’) 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                linjiqin  


   

六、rollup()与cube():排列组合分组 
1)、group by rollup(a, b, c):
先是会对(a、b、c)实行group by,
下一场再对(a、b)实行group by,
然后再对(a)举行group by,
终极对全表举办集中操作。

     

2)、group by cube(a, b, c):
则第一会对(a、b、c)进行group by,
然后家家户户是(a、b),(a、c),(a),(b、c),(b),(c),
末尾对全表进行聚集操作。

   

1、生成演示数据:
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 
Connected as ds_trade
 
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 
Connected as SYS
 
SQL> create table scott.t as select * from dba_indexes;
 
Table created
 
 
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release
10.2.0.3.0 
Connected as scott
 
SQL>

    

2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by owner, index_type, status;

金沙糖果派对网站app 92

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C):
首先会对(A、B、C)进行GROUP BY,
接下来再对(A、B)举行GROUP BY,
日后再对(A)实行GROUP BY,
谈到底对全表进行集中操作。
sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by ROLLUP(owner, index_type, status);

金沙糖果派对网站app 93

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C):
则率先会对(A、B、C)进行GROUP BY,
下一场依次是(A、B),(A、C),(A),(B、C),(B),(C),
最后对全表进行聚集操作。

sql> select owner, index_type, status, count(*) from t where owner
like ‘SY%’ group by cube(owner, index_type, status);

金沙糖果派对网站app 94

  

七、max(),min(),sun()与avg():求移动的最值总和与平均值
难题:总结出各种地点接连八个月的打电话开支的平平均数量(移动平均值)

 

金沙糖果派对网站app 95

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum", 
       AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg", 
       MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max", 
       MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH)

金沙糖果派对网站app 96

运作结果:

金沙糖果派对网站app 97

  

标题:求外省段按月度增加的话费

金沙糖果派对网站app 98

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

金沙糖果派对网站app 99

运转结果:

金沙糖果派对网站app 100

 


Blog:
J2EE、Android、Linux、Oracle QQ交流群:142463980、158560018(满)

另见:《Oracle深入分析函数ROW_NUMBE奥迪Q7()|RANK()|LAG()使用详解》

一、Oracle深入分析函数入门

 

分析函数是怎么样?
深入分析函数是Oracle特地用来缓和复杂报表计算供给的功用庞大的函数,它能够在数据中举行分组然后计算基于组的某种总结值,况兼每一组的每一行都得以回来三个总结值。

          

解析函数和聚合函数的不一致之处是什么样?
数见不鲜的聚合函数用group
by分组,每种分组重回四个总结值,而深入分析函数选拔partition
by分组,而且每组每行都得以回来一个计算值。

              

深入分析函数的款式
浅析函数带有二个开窗函数over(),满含四个剖判子句:分组(partition by),
排序(order by), 窗口(rows) ,
他们的应用方式如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在这里本身只说rows格局的窗口,range方式和滑动窗口也不提

    

剖析函数例子(在scott用户下模拟)

演示目标:突显各部门职员和工人的薪俸,并顺便呈现该部分的万丈薪水。

金沙糖果派对网站app 101

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

金沙糖果派对网站app 102

运作结果:

金沙糖果派对网站app 103

               

身体力行目标:依据deptno分组,然后总结每组值的总的数量

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运行结果:

金沙糖果派对网站app 104

     

身体力行目标:对各单位打开分组,并顺便展现第一行至当前行的汇总

金沙糖果派对网站app 105

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 106

运维结果:

金沙糖果派对网站app 107

   

示范指标:当前行至最终一行的集聚

金沙糖果派对网站app 108

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 109

运行结果:

金沙糖果派对网站app 110

   

 示例目的:当前行的上一行(rownum-1)到前段时间行的集中

金沙糖果派对网站app 111

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 112

运作结果:

金沙糖果派对网站app 113

    

躬行实践指标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的聚集     

金沙糖果派对网站app 114

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

金沙糖果派对网站app 115

运作结果:

金沙糖果派对网站app 116

      

 

 

防止这种状态,能够采纳开窗函数。

 

心得:
rank()跳跃排序,有四个第二名时前边跟着的是第四名
dense_rank() 两次三番排序,有多少个第二名时依旧跟着第三名

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

admin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注