函数之分析函数

-->黑木崖上的蜗牛

-->2009.04.28

 

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

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

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

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

 

-->测试环境

Code
 1
 2create   table  t_emp(id numeric(  10  )
 3  ,name  varchar (  10  ),
 4salary numeric(  10  ,  2  ));
 5
 6insert   into  t_emp  values (  1  ,  '  jack  '  ,  10000.50  );
 7insert   into  t_emp  values (  2  ,  '  jack2  '  ,  8000.50  );
 8insert   into  t_emp  values (  3  ,  '  jack3  '  ,  120000  );
 9insert   into  t_emp  values (  4  ,  '  jack4  '  ,  5000  );
10insert   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
1id                                      name       salary                                  rank                 rank1
2--------------------------------------- ---------- ---------------------------------------
34                                       jack4      5000.00                                 4                    2
49                                       jack4      5000.00                                 4                    2
54                                       jack4      5000.00                                 4                    2
61                                       jack       10000.50                                2                    4
76                                       jack       10000.50                                2                    4
81                                       jack       10000.50                                2                    4

 

3 .RANK()
排名问题

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

 

Code
 1id                                      name       salary                                 
 2--------------------------------------- ---------- ---------------------------------------
 33                                       jack3      120000.00                               1
 48                                       jack3      120000.00                               1
 56                                       jack        10000.50                                 3
 61                                       jack        10000.50                                 3
 72                                       jack2      8000.50                                   5
 87                                       jack2      8000.50                                   5
 99                                       jack4      5000.00                                   7
104                                       jack4      5000.00                                   7
115                                       jack5      3000.00                                   9
1210                                      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
3id                                      name       salary
4--------------------------------------- ---------- ---------------------------------------
52                                        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
1id                                      name       salary                                  cnt
2----------------------------- ---------- --------------------------------------- --------------------
33                                       jack3      120000.00                               1
48                                       jack3      120000.00                               1
53                                       jack3      120000.00                               1
61                                       jack        10000.50                                 2
76                                       jack        10000.50                                 2