row_number() 、dense_rank() 、rank() 、Over、Pivot用法说明

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