rank() over (partition by xx order by xx)

1、partition  by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

2、Rank 是在每个分组内部进行排名的。

 

例如：查询emp表中每个部门中工资前三名的员工的全部字段

 

SQL> select * from (select e.*,rank() over (partition by deptno order by sal) rk from emp e) t1 where t1.rk<=3;

 

EMPNO ENAME      JOB           MGR HIREDATE          SAL   COMM     DEPTNO         RK

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

 7934 MILLER     CLERK        7782 23-1月 -82       1300            10          1

 7782 CLARK      MANAGER      7839 09-6月 -81       2450            10          2

 7839 KING       PRESIDENT         17-11月-81       5000            10          3

 7369 SMITH      CLERK        7902 17-12月-80        800            20          1

 7566 JONES      MANAGER      7839 02-4月 -81       2975            20          2

 7902 FORD       ANALYST      7566 03-12月-81       3000            20          3

 7900 JAMES      CLERK        7698 03-12月-81        950            30          1

 7521 WARD       SALESMAN     7698 22-2月 -81       1250    500     30          2

 7654 MARTIN     SALESMAN     7698 28-9月 -81       1250   1400     30          2

 

已选择9行。

 

 

rank和dense_rank

根据order by的内容依次赋予递增序列

rank 和 dense_rank区别:

他们遇到相同值不会递增 都显示当前序列值

遇到相同值rank会跳号,dense_rank不跳号

但下一个不同的值出现时:

rank:   会累计

dense_rank:不会累计

 

SQL> break on deptno skip 1

SQL> select deptno,

   ename,

   sal,

   rank() over(partition by deptno order by sal desc) rank_sal,

   dense_rank() over(partition by deptno order by sal desc) dense_rank 

     from emp;

 

    DEPTNO ENAME     SAL   RANK_SAL DENSE_RANK

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

10 KING    5000  1     1

  CLARK    2450  2     2

  MILLER    1300  3     3

 

20 SCOTT    3000  1     1

  FORD    3000  1     1

  JONES    2975  3     2

  ADAMS    1100  4     3

  SMITH     800  5     4

 

30 BLAKE    2850  1     1

  ALLEN    1600  2     2

  TURNER           1500  3     3

  MARTIN           1250  4     4

  WARD    1250  4     4

  JAMES     950  6     5

 

 

14 rows selected.

 

 

 

Rank() over()的用法

创建一个test表，并插入6条数据。

CREATE TABLE test
(
	a INT,
	b INT,
	c CHAR
)
INSERT INTO test VALUES(1,3,'E')
INSERT INTO test VALUES(2,4,'A')
INSERT INTO test VALUES(3,2,'D')
INSERT INTO test VALUES(3,5,'B')
INSERT INTO test VALUES(4,2,'C')
INSERT INTO test VALUES(2,4,'B')

 

 

SELECT * from test

 

 

a           b           c
----------- ----------- ----
1           3           E
2           4           A
3           2           D
3           5           B
4           2           C
2           4           B

(6 行受影响)

1、整个结果集是一个分组，以a进行排名

SELECT a,b,c,rank () OVER (ORDER BY a) rank FROM test

 

 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    2
2           4           B    2
3           2           D    4
3           5           B    4
4           2           C    6

(6 行受影响)

2、整个结果集是一个分组，以b进行排名

SELECT a,b,c,rank () OVER (ORDER BY b) rank FROM test

 

 

a           b           c    rank
----------- ----------- ---- --------------------
3           2           D    1
4           2           C    1
1           3           E    3
2           4           A    4
2           4           B    4
3           5           B    6

(6 行受影响)

3、以a,b进行分组，在每个组内以b进行排名。分了5个组，第2行跟第3行是一个组，其他的每行是一个组。在第2行与第3行的组内以b排名，并列为1

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY b) rank FROM test

 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    1
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

4、以a,b进行分组，在每个组内以c进行排名。分了5个组，第2行跟第3行是一个组，其他的每行是一个组。在第2行与第3行的组内以c排名，由于c列一个是A，一个是B，所以Rank分别为1、2。

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY c) rank FROM test

 

 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    2
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

总结：1、partition  by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

2、Rank 是在每个分组内部进行排名的。

Technorati 标签: rank over,partition,sql

 

分类: SQL SERVER