数据库SQL中对查询结果排序排列序号编号，Oracle分析函数 rank,dense_rank,row_number使用和区别

Oracle从8i开始就提供了3个分析函数：rank，dense_rank，row_number


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


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


例:
create table s_score
( s_id number(6)
 ,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);

select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score;

   S_ID  SCORE       RANK DENSE_RANK ROW_NUMBER
------- ------ ---------- ---------- ----------
      3  99.00          1          1          1
      1  98.00          2          2          2
      4  98.00          2          2          3
      5  98.00          2          2          4
      6  80.00          5          3          5
      2  66.50          6          4          6
        
排名/排序的时候，有时候，我们会想到利用伪列rownum，利用rownum确实可以解决某些场景下的问题(但是相对也比较复杂)，而且有些
场景下的问题却很难解决。

例:取成绩前三名，并且前三名含有并列的情况    
通过上面例子，我们可以直观的看到，结果应该有5条记录。
select
    s_id 
   ,score
   ,dense_rank
from (
select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score
) t
where dense_rank <= 3;

   S_ID  SCORE DENSE_RANK
------- ------ ----------
      3  99.00          1
      1  98.00          2
      5  98.00          2
      4  98.00          2
      6  80.00          3
      
如果只是简单的想到去用rownum <= 3 得到的结果显然不可能是正确的。

组内的排名或者排序是经常遇到的一种场景。
例如，取每个销售部门内，销售业绩最好的前三名。
      取每个班级内成绩排名信息等等..
      
取每个班级内每门课成绩排名第一的同学信息     
drop table S_SCORE;
create table S_SCORE
(
  S_ID  NUMBER(6),
  CLASS_ID VARCHAR2(2),
  COURSE VARCHAR2(20),
  SCORE NUMBER(5,2)
);

INSERT INTO S_SCORE VALUES(1001,'A','MATH','67');
INSERT INTO S_SCORE VALUES(1004,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1002,'A','MATH','99');
INSERT INTO S_SCORE VALUES(1003,'A','MATH','55');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','70');
INSERT INTO S_SCORE VALUES(1001,'A','ORACLE','97');
INSERT INTO S_SCORE VALUES(1004,'B','ORACLE','48');
INSERT INTO S_SCORE VALUES(1002,'A','ORACLE','79');
INSERT INTO S_SCORE VALUES(1003,'A','ORACLE','65');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','82');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','78');

select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
;

   S_ID CLASS_ID COURSE                 SCORE        DRK
------- -------- -------------------- ------- ----------
   1002 A        MATH                   99.00          1
   1001 A        MATH                   67.00          2
   1003 A        MATH                   55.00          3
   1001 A        ORACLE                 97.00          1
   1002 A        ORACLE                 79.00          2
   1003 A        ORACLE                 65.00          3
   1004 B        MATH                   88.00          1
   1001 B        MATH                   88.00          1
   1001 B        MATH                   70.00          2
   1001 B        ORACLE                 82.00          1
   1001 B        ORACLE                 78.00          2
   1004 B        ORACLE                 48.00          3
   
select
   s_id
  ,class_id
  ,course
  ,score
from (
select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
) t
where drk = 1
;


   S_ID CLASS_ID COURSE                 SCORE
------- -------- -------------------- -------
   1002 A        MATH                   99.00
   1001 A        ORACLE                 97.00
   1004 B        MATH                   88.00
   1001 B        MATH                   88.00
   1001 B        ORACLE                 82.00   
   
rank()和dense_rank()用法相似，这里就不在举例说明了。可以将上面的例子中dense_rank()替换成rank()实现。


接下来,看一个使用row_number()的场景
例:查看每个部门最近一笔销售记录
select * from criss_sales order by dept_id,sale_date desc;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D01     2014/4/30   G03                800
D01     2014/4/8    G01                200
D01     2014/3/4    G00                700
D02     2014/5/2    G03                900
D02     2014/4/27   G01                300
D02     2014/4/8    G02                100
D02     2014/3/6    G00                500

即,我们希望得到
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900
这两条记录

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc)
from criss_sales;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT ROW_NUMBER()OVER(PARTITIONBYDE
------- ----------- ---------- ----------- ------------------------------
D01     2014/5/4    G02                 80                              1
D01     2014/4/30   G03                800                              2
D01     2014/4/8    G01                200                              3
D01     2014/3/4    G00                700                              4
D02     2014/5/2    G03                900                              1
D02     2014/4/27   G01                300                              2
D02     2014/4/8    G02                100                              3
D02     2014/3/6    G00                500                              4

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
from (
select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc) rn
from criss_sales
) t
where rn = 1
;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900

---------------------------------------------------------------------------------------------------------------

--在部门内新水排名（如果有相同名次，用dense_rank)
SELECT row_number() over
(PARTITION BY t.deptno  --按部门分组
ORDER BY t.sal) rn, --部门内按薪水排序
 t.sal, t.ename, t.deptno 
  FROM emp t;
--分组累计计数，注意相同sal情况
SELECT COUNT(*) over(PARTITION BY t.deptno ORDER BY sal) cn, t.sal, t.ename, t.deptno 
FROM emp t;


一、排名函数
1>、计算行号ROW_NUMBER函数
2>、排名和密集排号RANK和DESN_RANK

--示例:
CREATE TABLE dbo.Sales
(
  empid VARCHAR(10) NOT NULL PRIMARY KEY,
  mgrid VARCHAR(10) NOT NULL,
  qty   INT         NOT NULL
);

INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES
  ('A', 'Z', 300),
  ('B', 'X', 100),
  ('C', 'X', 200),
  ('D', 'Y', 200),
  ('E', 'Z', 250),
  ('F', 'Z', 300),
  ('G', 'X', 100),
  ('H', 'Y', 150),
  ('I', 'X', 250),
  ('J', 'Z', 100),
  ('K', 'Y', 200);

CREATE INDEX idx_qty_empid ON dbo.Sales(qty, empid);
CREATE INDEX idx_mgrid_qty_empid ON dbo.Sales(mgrid, qty, empid);
GO
###对比###
select ROW_NUMBER() OVER (ORDER BY qty) AS seq,
RANK()OVER (ORDER BY qty) AS seq1,
 DENSE_RANK() OVER (ORDER BY qty) AS seq2,
 *
FROM Sales s			
--运行结果

seq	seq1	seq2	empid	mgrid	qty
1	1	1	B	X	100
2	1	1	G	X	100
3	1	1	J	Z	100
4	4	2	H	Y	150
5	5	3	C	X	200
6	5	3	D	Y	200
7	5	3	K	Y	200
8	8	4	E	Z	250
9	8	4	I	X	250
10	10	5	A	Z	300
11	10	5	F	Z	300


区别:当order by 列表不能唯一决定排序顺序时，ROW_NUMBER是非确定性的。而RANK和DENSE_RANK总是确定性的。即,具有相同排序值的行总是得到相同的排名值。而RANK和DENSE_RANK的区别在于,RANK生成的排名值可能有间断，但可以表明有多少行具有更低的排序值。DENSE_RANK生成的排名值没有间断。



--分析函数：
count(a) over (partition by b order by c)
--上面的count()就是一个分析函数；over可以理解为一个关键字或者标识，有over就表示它前面的函数
--是一个分析函数，否则就是普通的求和函数了；
--（）中的partition by是指定分区，或者理解为分组
--（）中的order by是指定该分区内的数据的顺序

参考：http://jingyan.baidu.com/article/597035521ff2ec8fc107404b.html