SQL2005的几个新函数

一 排序函数 

row_number() ＋1递增返回行号 
dense_rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级数量等级的排名+1 
rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级所有数据总数+1 
语法 
function_name() OVER ( [ <partition_by_clause> ] <order_by_clause> ) 


CREATE TABLE employee (depart_name varchar(10),work_no varchar(6),salary int ) 

depart_name work_no salary 
财务部 200001 2000 
财务部 200002 2500 
财务部 200004 2500 
发展部 300002 2000 
发展部 300009 1800 
发展部 300014 2000 
综合部 400001 1800 
综合部 400005 2000 
综合部 400009 2500 
技术部 500008 2500 
技术部 500099 1800 
技术部 500102 2700 

--按工资从低到高排名 
SELECT row_number() OVER (ORDER BY salary ) as id, 
dense_rank() OVER (ORDER BY salary desc) as 工资排名1, 
rank() OVER (ORDER BY salary desc) as 工资排名2, 
* FROM employee 

ID 工资排名1 工资排名2 部门名 工号 工资 
1 1 1 发展部 300009 1800 
2 1 1 综合部 400001 1800 
3 1 1 技术部 500099 1800 
4 2 4 发展部 300002 2000 
5 2 4 综合部 400005 2000 
6 2 4 发展部 300014 2000 
7 2 4 财务部 200001 2000 
8 3 8 财务部 200002 2500 
9 3 8 财务部 200004 2500 
10 3 8 综合部 400009 2500 
11 3 8 技术部 500008 2500 
12 4 12 技术部 500102 2700 

--分部门按工资从低到高排名 
SELECT row_number() OVER (PARTITION BY depart_name ORDER BY salary ) as 工资排名, 
* FROM employee 

工资排名 部门名 工号 工资 
1 财务部 200001 2000 
2 财务部 200002 2500 
3 财务部 200004 2500 
－－－－－－－－－－－－－－－－－－－ 
1 发展部 300009 1800 
2 发展部 300014 2000 
3 发展部 300002 2000 
－－－－－－－－－－－－－－－－－－－ 
1 技术部 500099 1800 
2 技术部 500008 2500 
3 技术部 500102 2700 
－－－－－－－－－－－－－－－－－－－ 
1 综合部 400001 1800 
2 综合部 400005 2000 
3 综合部 400009 2500 



二 行列转换 

CREATE TABLE E_money (work_no varchar(6),moeny_type varchar(10),num int ) 

work_no money_type num 
100001 HKD 100 
100001 HKD 200 
100001 USD 300 
200020 HKD 50 
200020 RMB 5000 
200020 USD 500 
400078 HKD 80 
400078 HKD 90 
400078 RMB 800 
400078 RMB 900 
400078 USD 90 
400078 USD 800 


--统计每个员工不同币种拥有数 
work_no 港币 美元 人民币 
100001 300 300 50 
200020 50 500 5000 
400078 170 890 1700 

--sql 2000写法 
select work_no, 
sum(case when moeny_type='HKD' then num else 0 end) as 港币, 
sum(case when moeny_type='USD' then num else 0 end) as 美元, 
sum(case when moeny_type='RMB' then num else 0 end) as 人民币 
from e_money 
group by work_no 

--2005新增函数 
SELECT work_no,[HKD] as 港币,[USD] as 美元,[RMB] as 人民币 FROM e_money 
PIVOT( 
SUM(num) 
FOR moeny_type IN 
([HKD],[USD],[RMB]) 
) AS pvt  

--ROW_NUMBER生成自增字段
SELECT ROW_NUMBER()OVER(ORDER BY EmployeeID) AS LineID,EmployeeID,EmployeeName
FROM BEmployee

--建立测试数据
DECLARE @t TABLE(stID INT,stName NVARCHAR(10),QTY INT)
INSERT INTO @t(stID,stName,QTY)
SELECT 1,N'aa',50 UNION ALL
SELECT 2,N'bb',60 UNION ALL
SELECT 3,N'bb',40 UNION ALL
SELECT 4,N'dd',75 UNION ALL
SELECT 5,N'aa',500 UNION ALL
SELECT 6,N'ff',520 UNION ALL
SELECT 7,N'gg',150 

--DENSE_RANK显示排名
SELECT DENSE_RANK() OVER(ORDER BY QTY DESC)AS LineID,stName,QTY
FROM @t

--RANK函数,显示排名与序列
SELECT RANK() OVER(ORDER BY QTY DESC)AS RankID,stID,stName,QTY
FROM @t

--NTILE分页排序
SELECT NTILE(4) OVER(ORDER BY QTY DESC)AS RankID,stID,stName,QTY
FROM @t

--同字段最小值筛选
SELECT ROW_NUMBER() OVER (ORDER BY stName ASC)as LineID,* 
FROM @T a 
WHERE NOT EXISTS(SELECT 1 FROM @t WHERE stName=a.stName AND QTY