2005的几个新函数
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
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