2005的几个新函数

一 排序函数 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

  1一 排序函数
  2
  3row_number() ＋1递增返回行号
  4dense_rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级数量等级的排名+1
  5rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级所有数据总数+1
  6语法
  7function_name() OVER ( [ <partition_by_clause> ] <order_by_clause> )
  8
  9
 10CREATE TABLE employee (depart_name varchar(10),work_no varchar(6),salary int )
 11
 12depart_name work_no     salary
 13财务部    2000012000
 14财务部    2000022500
 15财务部    2000042500
 16发展部    3000022000
 17发展部    3000091800
 18发展部    3000142000
 19综合部    4000011800
 20综合部    4000052000
 21综合部    4000092500
 22技术部    5000082500
 23技术部    5000991800
 24技术部    5001022700
 25
 26--按工资从低到高排名
 27SELECT row_number() OVER (ORDER BY salary ) as id,
 28dense_rank() OVER (ORDER BY salary desc) as 工资排名1,
 29rank() OVER (ORDER BY salary desc) as 工资排名2,
 30* FROM employee
 31
 32ID  工资排名1 工资排名2 部门名   工号   工资
 33111发展部3000091800
 34211综合部4000011800
 35311技术部5000991800
 36424发展部3000022000
 37524综合部4000052000
 38624发展部3000142000
 39724财务部2000012000
 40838财务部2000022500
 41938财务部2000042500
 421038综合部4000092500
 431138技术部5000082500
 4412412技术部5001022700
 45
 46--分部门按工资从低到高排名
 47SELECT row_number() OVER (PARTITION BY depart_name ORDER BY salary ) as 工资排名,
 48* FROM employee
 49
 50工资排名  部门名        工号    工资
 511  财务部2000012000
 522  财务部2000022500
 533  财务部2000042500
 54－－－－－－－－－－－－－－－－－－－
 551  发展部3000091800
 562  发展部3000142000
 573  发展部3000022000
 58－－－－－－－－－－－－－－－－－－－
 591  技术部5000991800
 602  技术部5000082500
 613  技术部5001022700
 62－－－－－－－－－－－－－－－－－－－
 631  综合部4000011800
 642  综合部4000052000
 653  综合部4000092500
 66
 67
 68
 69二  行列转换
 70
 71CREATE TABLE E_money (work_no varchar(6),moeny_type varchar(10),num int )
 72
 73work_no money_type num
 74100001HKD   100
 75100001HKD   200
 76100001USD   300
 77200020HKD   50
 78200020RMB   5000
 79200020USD   500
 80400078HKD   80
 81400078HKD   90
 82400078RMB   800
 83400078RMB   900
 84400078USD   90
 85400078USD   800
 86
 87
 88--统计每个员工不同币种拥有数
 89work_no 港币    美元    人民币
 9010000130030050
 91200020505005000
 924000781708901700
 93
 94--sql 2000写法
 95select work_no,
 96sum(case when moeny_type='HKD' then num else 0 end) as 港币,
 97sum(case when moeny_type='USD' then num else 0 end) as 美元,
 98sum(case when moeny_type='RMB' then num else 0 end) as 人民币
 99from e_money 
100group by work_no
101
102--2005新增函数
103SELECT work_no,[HKD] as 港币,[USD] as 美元,[RMB] as 人民币 FROM e_money
104PIVOT(
105SUM(num) 
106FOR moeny_type IN
107([HKD],[USD],[RMB])
108) AS pvt
109
110
111