-- 近几天，观察了一下公司的统计平台，经常看到类似如下的ROW_NUMBER()函数相关的SQL语句：

SELECT ...
       ROW_NUMBER() OVER (PARTITION BY T74.WEEK_NAME, T5816.SESSION_ID ORDER BY T74.WEEK_NAME DESC, T5816.SESSION_ID DESC)
       ...
FROM   ...

-- 很明显：ROW_NUMBER() OVER()函数中的 ORDER BY 部分完全与 PARTITION BY 部分相同。
-- 我当时很疑惑：这样查询出来的结果能否准确呢？是否是你想要的数据呢？

-- 个人提示：用 ROW_NUMBER() OVER()函数的时候，其ORDER BY 所指定的字段，不应该与 PARTITION BY 所指定的字段完全相同，
--           准确地说：已经在PARTITION BY 字段中指定的字段，就不应该在后续的 ORDER BY 字段中出现。

-- 举例：
-- Emp表字段说明：
 -----------------------------------------
 EMPNO       NOT NULL NUMBER(4)       -- 员工号
 ENAME              VARCHAR2(10)    -- 员工名称
 JOB              VARCHAR2(9)     -- 员工职位
 MGR              NUMBER(4)       -- 员工上级领导工号
 HIREDATE          DATE            -- 员工入职日期
 SAL              NUMBER(7,2)     -- 员工薪水
 COMM              NUMBER(7,2)     -- 员工提成
 DEPTNO           NUMBER(2)       -- 员工所在部门的部门号

scott@TDODS> SELECT * FROM EMP;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO DESC) AS cnt
FROM EMP;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO         CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10           1
      7839 KING       PRESIDENT        17-NOV-81       5000            10           2
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10           3
      7566 JONES      MANAGER          7839 02-APR-81       2975            20           1
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20           2
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20           3
      7369 SMITH      CLERK          7902 17-DEC-80        800            20           4
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20           5
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30           1
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30           2
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30           3
      7900 JAMES      CLERK          7698 03-DEC-81        950            30           4
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30           5
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400