MS-SQL Server2005的几个新函数

一 排序函数

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
财务部    2000012000
财务部    2000022500
财务部    2000042500
发展部    3000022000
发展部    3000091800
发展部    3000142000
综合部    4000011800
综合部    4000052000
综合部    4000092500
技术部    5000082500
技术部    5000991800
技术部    5001022700

--按工资从低到高排名
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 部门名   工号   工资
111发展部3000091800
211综合部4000011800
311技术部5000991800
424发展部3000022000
524综合部4000052000
624发展部3000142000
724财务部2000012000
838财务部2000022500
938财务部2000042500
1038综合部4000092500
1138技术部5000082500
12412技术部5001022700

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

工资排名  部门名        工号    工资
1  财务部2000012000
2  财务部2000022500
3  财务部2000042500
－－－－－－－－－－－－－－－－－－－
1  发展部3000091800
2  发展部3000142000
3  发展部3000022000
－－－－－－－－－－－－－－－－－－－
1  技术部5000991800
2  技术部5000082500
3  技术部5001022700
－－－－－－－－－－－－－－－－－－－
1  综合部4000011800
2  综合部4000052000
3  综合部4000092500

二  行列转换

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

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

--统计每个员工不同币种拥有数
work_no 港币    美元    人民币
10000130030050
200020505005000
4000781708901700

--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