函数之分析函数

 

 

row_number()： 主要是分页，查询

DENSE_RANK() ：排名（顺序始终是连续的）,适合刚才的取出薪水排名（有重复的)

RANK()：排名（列有重复，区间就是不连续的,如:1,2,2,4,4,6...)

NTILE（N）：分组函数，把记录强制分成N段

 

-->测试环境

Code
 1
 2 create     table   t_emp(id numeric(   10   )
 3   ,name   varchar  (   10   ),
 4 salary numeric(   10   ,   2   ));
 5
 6 insert     into   t_emp   values  (   1   ,   '   jack   '   ,   10000.50   );
 7 insert     into   t_emp   values  (   2   ,   '   jack2   '   ,   8000.50   );
 8 insert     into   t_emp   values  (   3   ,   '   jack3   '   ,   120000   );
 9 insert     into   t_emp   values  (   4   ,   '   jack4   '   ,   5000   );
10 insert     into   t_emp   values  (   5   ,   '   jack5   '   ,   3000   );  

 
1.Row_number()

用来选择行或分页 操作；

查询操作

select id,name salary,row_number() over(paritition by name order by name) Rownumber

from t_emp;

有时用来过滤重复行；

 

2. DENSE_RANK()

--找出工资第二跟倒数第二人员的员工

select * from
(select emp.*, DENSE_RANK() over (order by salary desc) as rank,
DENSE_RANK() over (order by salary asc) as rank1
 from t_emp emp)  t
where (rank=2 or rank1=2)

 

名次会有重复;

Code
1 id                                      name       salary                                  rank                 rank1
2 -- ------------------------------------- ---------- ---------------------------------------
3 4                                        jack4       5000.00                                   4                      2
4 9                                        jack4       5000.00                                   4                      2
5 4                                        jack4       5000.00                                   4                      2
6 1                                        jack        10000.50                                  2                      4
7 6                                        jack        10000.50                                  2                      4
8 1                                        jack        10000.50                                  2                      4

 

3 .RANK()
排名问题

SELECT *,RANK() OVER ( ORDER BY t_emp.salary DESC ) FROM t_emp

 

Code
 1 id                                      name       salary                                 
 2 -- ------------------------------------- ---------- ---------------------------------------
 3 3                                        jack3       120000.00                                 1
 4 8                                        jack3       120000.00                                 1
 5 6                                        jack         10000.50                                   3
 6 1                                        jack         10000.50                                   3
 7 2                                        jack2       8000.50                                     5
 8 7                                        jack2       8000.50                                     5
 9 9                                        jack4       5000.00                                     7
10 4                                        jack4       5000.00                                     7
11 5                                        jack5       3000.00                                     9
12 10                                       jack5      3000.00                                     9
13
14
15 ( 10  行受影响)
16
17
18

注:查询名次可以用上面的算法,

改进算法:

如查询第二名;

select * from t_emp A
where 1 = (select COUNT(1) from t_emp where a.salary < salary )

 

Code
1 /**/ /*
2
3 id                                      name       salary
4 --------------------------------------- ---------- ---------------------------------------
5 2                                        jack2     8000.50
6
7 (1 行受影响)
8
9 */

 

 

4.NTILE(n)

 

SELECT TOP 5 *,NTILE(5) OVER ( ORDER BY t_emp.salary DESC ) FROM t_emp

 

Code
1 id                                      name       salary                                  cnt
2 -- --------------------------- ---------- --------------------------------------- --------------------
3 3                                        jack3       120000.00                                 1
4 8                                        jack3       120000.00                                 1
5 3                                        jack3       120000.00                                 1
6 1                                        jack         10000.50                                   2
7 6                                        jack         10000.50                                   2