关于查询排名

先看个例子，以hr用户下的employees表为例子，查询工资金额（这里不算佣金）在第四名至第七名之间的员工的信息。
             第一种方法，利用ORACLE分析函数，我们分几步来写出这个SQL：
           1，select employee_id,first_name,last_name,salary, 
                 rank() over (order by salary desc) as rank
                from employees group by employee_id,first_name,last_name,salary;
            得到如下结果：
                

                      可以看出此时的employee_id是按照salary的多少进行排序的，注意后面的rank_salary的名次是并列的（有了两个salary相同的第二名，接下来的是第四名），这个地方如果用dense_rank()，则在两个第二名后仍是第三名。
        2，进行子查询：
       select * from ( select employee_id,first_name,last_name,salary, 
                 rank() over (order by salary desc) as rank
                from employees group by employee_id,first_name,last_name,salary)  tt where tt.rank>3 and tt.rank<8;
       执行结果：
          

                   注意：rank函数那一列的别名万不可包含salary，它是ORACLE的关键字，使用了它，执行子查询时，总会提示这个别名标识符有误！（在sys用户名下使用select * from v$reserved_words查看关键字）

            第二种方法，介绍下ORACLE的伪列rownum
      1，我们可以使用rownum来控制查询语句返回的行数：
        select * from employees where rownum<5;
       

                 看到这里可能有人要问了，既然可以控制只返回前4行，那么可不可以控制返回4行以后的呢？我们来试试：
            select * from employees where rownum>5;
      返回结果：
          

                    很遗憾，什么都没有！这是为什么呢？这就跟rownum的性质有关了，rownum在提取行时，总是以1开始的，如果第一条不满足，那么取第二条，此时第二条又变成了1，第二条不满足，再取下一条，还是以1开始......所以，用>去检索rownum，必然是什么都检索不到。只有两种情况例外，就是当rownum>0或者rownum>=1是有结果显示的，而且是显示的所有行;当rownum=1时，只显示一行，即整个表的第一行。
        本来我们开始本想使用select .....from......order by ......where rownum>3 and rownum<8句子来实现排名工资的，现在已得知rownum不能使用>,那么是不是就没有办法了吗？我试了试这种方法：
   1，select * from (select * from employees order by salary desc) aa where rownum<8;
        

 

              这里补充下，可能有人会问我为什么要用上内嵌视图，为什么不写成
          select * from employees order  by salary desc where rownum<8这样执行，会报错。因为rownum的序号是在建表插入数据时系统指定的，哪行被第一个插入，哪个数据的ROWNUM就为1，举个例子，我执行如下语句：
select rownum,employee_id,first_name,first_name,salary from employees order by salary desc;

           执行结果：

         

           可以看出，rownum并不是我们想象的那样，取前七行就行了。而使用内嵌视图，相当于上面这个表就是源表，因为相当于新建了一张表，ROWNUM这个时候就是按EMPLOYEE_ID的顺序来排列的，然后在用rownum取前七行就行了。


               2，select * from
            (select rownum vv,employee_id,first_name,last_name,salary
             from (select * from employees order by salary desc) aa where rownum<8) bb
              where bb.vv>3;
               执行结果：

            

           这也是排名，从第四名到第七名的顺序，但是rownum有一个致命的问题：如有有并列的怎么办？你又怎么知道并列的行是第排rownum？所以这种排名之类的东西，还是要用分析函数写。