-->黑木崖上的蜗牛
-->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
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/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