一个员工信息表

Create Table EmployeeInfo (CODE Number(3) Not Null,EmployeeName varchar2(15),DepartmentID Number(3),Salary NUMBER(7,2),

Constraint PK_EmployeeInfo Primary Key (CODE));

Select * From EMPLOYEEINFO

现执行SQL语句：

Select EMPLOYEENAME,SALARY,

RANK() OVER (Order By SALARY Desc)  "RANK",

DENSE_RANK() OVER (Order By SALARY Desc ) "DENSE_RANK",

ROW_NUMBER() OVER(Order By SALARY Desc) "ROW_NUMBER"

 From EMPLOYEEINFO

结果如下：

Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

①ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

②DENSE_RANK：

Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

③RANK：

Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

同时也可以分组排序,也就是在Over从句内加入Partition by groupField：

 Select DEPARTMENTID,EMPLOYEENAME,SALARY,

RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc)  "RANK",

DENSE_RANK() OVER ( Partition By DEPARTMENTID Order By SALARY Desc ) "DENSE_RANK",

ROW_NUMBER() OVER( Partition By DEPARTMENTID Order By SALARY Desc) "ROW_NUMBER"

 From EMPLOYEEINFO

结果如下：

现在如果插入一条工资为空的记录，那么执行上述语句，结果如下：

会发现空值的竟然排在了第一位，这显然不是想要的结果。解决的办法是在Over从句Order By后加上 NULLS Last即：

Select EMPLOYEENAME,SALARY,

RANK() OVER (Order By SALARY Desc  Nulls Last)  "RANK",

DENSE_RANK() OVER (Order By SALARY Desc Nulls Last) "DENSE_RANK",

ROW_NUMBER() OVER(Order By SALARY Desc Nulls Last ) "ROW_NUMBER"

 From EMPLOYEEINFO

结果如下：

实验
2011-10-15 Top-N 查询
作者：Tim Hall
难度：中

下列代码创建了员工表和数据：
CREATE TABLE plch_emp (
empno     NUMBER(4,0),
ename     VARCHAR2(10 BYTE),
job       VARCHAR2(9 BYTE),
mgr       NUMBER(4,0),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2,0)
);

INSERT INTO plch_emp VALUES (7369,'SMITH', 'CLERK',    7902
   ,TO_DATE('17-DEC-80','DD-MON-RR'), 800,NULL,20);
INSERT INTO plch_emp VALUES (7900,'JAMES', 'CLERK',    7698
   ,TO_DATE('03-DEC-81','DD-MON-RR'), 950,NULL,30);
INSERT INTO plch_emp VALUES (7876,'ADAMS', 'CLERK',    7788
   ,TO_DATE('23-MAY-87','DD-MON-RR'),1100,NULL,20);
INSERT INTO plch_emp VALUES (7521,'WARD',  'SALESMAN', 7698
   ,TO_DATE('22-FEB-81','DD-MON-RR'),1250, 500,30);
INSERT INTO plch_emp VALUES (7654,'MARTIN','SALESMAN', 7698
   ,TO_DATE('28-SEP-81','DD-MON-RR'),1250,1400,30);
INSERT INTO plch_emp VALUES (7844,'TURNER','SALESMAN', 7698
   ,TO_DATE('08-SEP-81','DD-MON-RR'),1500,   0,30);
INSERT INTO plch_emp VALUES (7934,'MILLER','CLERK',    7782
   ,TO_DATE('23-JAN-82','DD-MON-RR'),1300,NULL,10);
INSERT INTO plch_emp VALUES (7499,'ALLEN', 'SALESMAN', 7698
   ,TO_DATE('20-FEB-81','DD-MON-RR'),1600, 300,30);
INSERT INTO plch_emp VALUES (7782,'CLARK', 'MANAGER',  7839
   ,TO_DATE('09-JUN-81','DD-MON-RR'),2450,NULL,10);
INSERT INTO plch_emp VALUES (7698,'BLAKE', 'MANAGER',  7839
   ,TO_DATE('01-MAY-81','DD-MON-RR'),2850,NULL,30);
INSERT INTO plch_emp VALUES (7566,'JONES', 'MANAGER',  7839
   ,TO_DATE('02-APR-81','DD-MON-RR'),2975,NULL,20);
INSERT INTO plch_emp VALUES (7902,'FORD',  'ANALYST',  7566
   ,TO_DATE('03-DEC-81','DD-MON-RR'),3000,NULL,20);
INSERT INTO plch_emp VALUES (7788,'SCOTT', 'ANALYST',  7566
   ,TO_DATE('19-APR-87','DD-MON-RR'),3000,NULL,20);
INSERT INTO plch_emp VALUES (7839,'KING',  'PRESIDENT',NULL
   ,TO_DATE('17-NOV-81','DD-MON-RR'),5000,NULL,10);
COMMIT;

下列哪些查询显示了薪水最高（忽略佣金commission）的五名员工？
(A)
SELECT e.ename,
       e.sal
FROM   plch_emp e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(B)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               DENSE_RANK() OVER (ORDER BY sal DESC) AS sal_rank
        FROM   plch_emp) e
WHERE  e.sal_rank <= 5
ORDER BY e.sal DESC, e.ename;

(C)
SELECT e.ename,
       e.sal
FROM   (SELECT ename, sal
        FROM   plch_emp
        ORDER BY sal DESC) e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(D)
WITH emp_query AS (
  SELECT ename, sal
  FROM   plch_emp
  ORDER BY sal DESC)
SELECT e.ename,
       e.sal
FROM   emp_query e
WHERE  rownum <= 5
ORDER BY e.sal DESC, e.ename;

(E)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               RANK() OVER (ORDER BY sal DESC) AS sal_rank
        FROM   plch_emp) e
WHERE  e.sal_rank <= 5
ORDER BY e.sal DESC, e.ename;


(F)
SELECT e.ename,
       e.sal
FROM   (SELECT ename,
               sal,
               ROW_NUMBER() OVER (ORDER BY sal ASC) AS sal_rank
        FROM   plch_emp) e

2011-10-15 答案CDE.
A: 未经排序引用ROWNUM是不正确的
B: DENSE_RANK用错了，会导致超过5人的也被选中，应该用RANK
F: ROW_NUMBER里面的排序应该用倒序才对。