函数之分析函数

 -->黑木崖上的蜗牛

-->2009.04.28

 

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

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

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

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

 

-->测试环境

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)

 

名次会有重复;

 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

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 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/you_tube/archive/2009/06/07/4137168.aspx

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

改进算法:

如查询第二名;

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

id                                      name       salary 

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

 2                                        jack2     8000.50

 (1 行受影响)

4.NTILE(n)

 

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

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