SQL实例进阶-学习sql server2005 step by step(八)
1.SQL2005中row_number( )、rank( )、dense_rank( )、ntile( )函数的用法
（1）.row_number( )
先来点数据,先建个表
代码
1 SET NOCOUNT ON
2
3 CREATE TABLE Person(
4
5 FirstName VARCHAR(10),
6
7 Age INT,
8
9 Gender CHAR(1))
10
11 INSERT INTO Person VALUES ('Ted',23,'M')
12
13 INSERT INTO Person VALUES ('John',40,'M')
14
15 INSERT INTO Person VALUES ('George',6,'M')
16
17 INSERT INTO Person VALUES ('Mary',11,'F')
18
19 INSERT INTO Person VALUES ('Sam',17,'M')
20
21 INSERT INTO Person VALUES ('Doris',6,'F')
22
23 INSERT INTO Person VALUES ('Frank',38,'M')
24
25 INSERT INTO Person VALUES ('Larry',5,'M')
26
27 INSERT INTO Person VALUES ('Sue',29,'F')
28
29 INSERT INTO Person VALUES ('Sherry',11,'F')
30
31 INSERT INTO Person VALUES ('Marty',23,'F')
32
33
直接用例子说明问题:
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],FirstName,Age
FROM Person
出现的数据如下
Row Number by Age FirstName Age
-------------------------- ---------- --------
1 Larry 5
2
3 George 6
4 Mary 11
5 Sherry 11
6 Sam 17
7 Ted 23
8 Marty 23
9 Sue 29
10 Frank 38
11 John 40
可以观察到,是根据年龄升序排列了，并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,与sql server2000对比:如果在sql server2000中实现相对麻烦一些，我们可以利用IDENTITY()函数实现，但IDENTITY()函数只能用在sql server2000临时表中，因此需要将数据检索到临时表里。select identity(int,1,1) as [Row Number by Age],FirstName,Age into #A from Person order by Ageselect * from #Adrop table #a如果不想按年龄排序,可以这样写
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],FirstName,Age
FROM Person另外一个例子SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName,Age,Gender
FROM Person这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下
Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1
2 Mary
3 Sherry
4 Sue
1 Larry
2 George
3 Sam
4 Ted
5 Marty
6 Frank
7 John
（2）.RANK( )函数
先看例子
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],FirstName,Age
FROM Person输出如下:Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2
2 George 6
4 Mary 11
4 Sherry 11
6 Sam 17
7 Ted 23
7 Marty 23
9 Sue 29
10 Frank 38
11 John 40
看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了。与sql server2000对比:出现了RANK()函数实在是方便,在sql server2000里实现排序并列的问题麻烦很多。
select [Rank by Age]=isnull((select count(*) from person where Age>A.Age),0)+1,FirstName,Age from Person A order
by [Rank by Age] SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName, Age, Gender FROM Person
输出为
Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1
2 Mary
2 Sherry
4 Sue
1 Larry
2 George
3 Sam
4 Ted
4 Marty
6 Frank
7 John
（3）.DENSE_RANK( )函数
SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age], FirstName, Age
FROM Person
输出结果为:
Dense Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40
看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了
（4）.ntile( )函数
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person
输出结果：
FirstName Age Age Groups
---------- ----------- --------------------
Larry 5 1
George 6 1
Mary 11 1
Sherry 11 2
Sam 17 2
Ted 23 2
Marty 23 2
Sue 29 3
Frank 38 3
John 40 3
这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段了。
2.SQLServer 2005 中的except/intersect和outer apply交并集计算
首先，建立两个表：
代码
1 CREATE TABLE #a (ID INT)
2 INSERT INTO #a VALUES (1)
3 INSERT INTO #a VALUES (2)
4 INSERT INTO #a VALUES (null)
5
6 CREATE TABLE #b (ID INT)
7 INSERT INTO #b VALUES (1)
8 INSERT INTO #b VALUES (3)
9
10
我们的目的是从表#b中取出ID不在表#a的记录。
如果不看具体的insert的内容，单单看这个需求，可能很多朋友就会写出这个sql了：
select * from #b where id not in (select id from #a)
但是根据上述插入的记录，这个sql检索的结果不是我们期待的ID=3的记录，而是什么都没有返回。原因很简单：在子查询select id from #a中返回了null，而null是不能跟任何值比较的。
那么您肯定会有下面的多种写法了：
代码
1 select * from #b where id not in (select id from #a where id is not null)
2 select * from #b b where b.id not in (select id from #a a where a.id=b.id)
3 select * from #b b where not exists (select 1 from #a a where a.id=b.id)
4
5
当然还有使用left join/right join/full join的几种写法，但是无一例外，都是比较冗长的。其实在SQL Server 2005增加了一种新的方法，可以帮助我们很简单、很简洁的完成任务：
select * from #b
except
select * from #a
我不知道在SQL Server 2008里还有没有什么更酷的方法，但是我想这个应该是最简洁的实现了。当然，在2005里还有一种方法可以实现：
1 select * from #b b
2 outer apply
3 (select id from #a a where a.id=b.id) k
4 where k.id is null
5
6
outer apply也可以完成这个任务。
如果我们要寻找两个表的交集呢？那么在2005就可以用intersect关键字：
select * from #b
intersect
select * from #a
ID
-----------
1
(1 row(s) affected)
0
3.使用coalesce和nullif的组合来减轻sql的工作
代码
1 create table tbl (id int, type_a int)
2
3 insert into tbl values (1000,1000)
4 insert into tbl values (999,999)
5 insert into tbl values (998,998)
6 insert into tbl values (997,997)
7 insert into tbl values (996,996)
8 insert into tbl values (995,null)
9 insert into tbl values (994,null)
10 insert into tbl values (993,null)
11 insert into tbl values (992,null)
12 insert into tbl values (991,null)
13
14
逻辑非常简单：当type_a为997或null的时候，我们要让输出的type_a字段值为0。
OK，这个SQL语句当然有多种写法，朋友的sql是这样写的：
1 select
2 case
3 when (type_a is null or type_a=997) then 0
4 else type_a
5 end as type_a
6 from tbl
7
8
如果需要控制的字段一多，那这个及时已经使用了缩进的select也看起来很复杂了，时间久了想改动这个sp的逻辑就有些吃力了，我们常常在做计划时会说“半小时搞定这个问题”，但是往往在做的时候都会超过这个时间，原因就在于我们总有从一团乱麻中找到入手点。复杂的代码和逻辑往往是解决问题中难啃的骨头。那么有什么好办法优化一下吗？
select coalesce(nullif(type_a,997),0) as type_a from tbl
Well，上面写了6行的sql就被这1行所替代了。
nullif接受两个参数，如果两个参数相等，那么返回null，否则返回第一个参数
coalesce接受N个参数，返回第一个不为null的参数
So，当您遇到处理一个如下所示的计算工资的问题的时候，不妨这样来解决：
create table salary (e_id uniqueidentifier, byMonth int, byHalfYear int, byYear int)
insert into salary values (newid(),9000,null,null)
insert into salary values (newid(),null,60000,null)
insert into salary values (newid(),null,null,150000)
每个雇员有3种薪资计算方式（按月，按半年，按年）来发放工资，如果我们想统计每个员工的年薪，那这样一句就够了：
select e_id,coalesce(byMonth*12,byHalfYear*2,byYear) as salary_amount from salary
结果：
e_id salary_amount
------------------------------------ -------------
8935330D-2B73-4FEF
06B6B924-EAB2-4187-B733-EBB56B62E793 150000
参考：
COALESCE (Transact-SQL)
NULLIF (Transact-SQL)
4.递归子查询（父子关系的）
代码
1 --测试数据
2
3 CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
4
5 INSERT tb SELECT '001',NULL ,'山东省'
6
7 UNION ALL SELECT '002','001','烟台市'
8
9 UNION ALL SELECT '004','002','招远市'
10
11 UNION ALL SELECT '003','001','青岛市'
12
13 UNION ALL SELECT '005',NULL ,'四会市'
14
15 UNION ALL SELECT '006','005','清远市'
16
17 UNION ALL SELECT '007','006','小分市'
18
19 GO
20
21
22
23 --查询指定节点及其所有子节点的函数
24
25 CREATE FUNCTION f_Cid(@ID char(3))
26
27 RETURNS @t_Level TABLE(ID char(3),Level int)
28
29 AS
30
31 BEGIN
32
33 DECLARE @Level int
34
35 SET @Level=1
36
37 INSERT @t_Level SELECT @ID,@Level
38
39 WHILE @@ROWCOUNT>0
40
41 BEGIN
42
43 SET @Level=@Level+1
44
45 INSERT @t_Level SELECT a.ID,@Level
46
47 FROM tb a,@t_Level b
48
49 WHERE a.PID=b.ID
50
51 AND b.Level=@Level-1
52
53 END
54
55 RETURN
56
57 END
58
59 GO
60
61
62
63 --调用函数查询002及其所有子节点
64
65 SELECT a.*
66
67 FROM tb a,f_Cid('002') b
68
69 WHERE a.ID=b.ID
70
71 /*--结果
72
73 ID PID Name
74
75 ------ ------- ----------
76
77 002 001 烟台市
78
79 004 002 招远市
80
------SqlServer2005
在SQL Server 2005数据库中，递归查询对于同一个表父子关系的计算提供了很大的方便，下文中的示例使用了SQL server 2005中的递归查询,使用的表是CarParts，这个表存储了一辆汽车的所有零件以及结构，part是零件单位，subpart是子零件，Qty是数量。
示例如下：
代码
1 */
2
3 CREATE table CarParts
4
5 (
6
7 CarID INT NOT NULL,
8
9 Part VARCHAR(15),
10
11 SubPart VARCHAR(15),
12
13 Qty INT
14
15 )
16
17 GO
18
19 INSERT CarParts VALUES (1, 'Body', 'Door', 4)
20
21 INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
22
23 INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
24
25 INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
26
27 INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
28
29 INSERT CarParts VALUES (1, 'Door', 'Window', 1)
30
31 INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
32
33 INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
34
35 INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
36
37 INSERT CarParts VALUES (1, 'Mirror', 'small_Mirror', 4)
38
39 GO
40
41 SELECT * FROM CarParts
42
43 GO
44
45 /*
46
47 一辆汽车需要各个零件的数目
48
49 1个Body 需要4个Door
50
51 1个Door 需要1个Mirror
52
53 那么
54
55 1个body需要4个Mirror
56
57 结构很简单吧
58
59 */
60
61 WITH CarPartsCTE(SubPart, Qty)
62
63 AS
64
65 (
66
67 -- 固定成员 (AM):
68
69 -- SELECT查询无需参考CarPartsCTE
70
71 -- 递归从此处开始
72
73 SELECT SubPart, Qty
74
75 FROM CarParts
76
77 WHERE Part = 'Body'
78
79 UNION ALL
80
81 -- 递归成员 (RM):
82
83 -- SELECT查询参考CarPartsCTE
84
85 -- 使用现有数据往下一层展开
86
87 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
88
89 FROM CarPartsCTE
90
91 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
92
93 WHERE CarParts.CarID = 1
94
95 )
96
97 SELECT SubPart,Qty AS TotalNUM
98
99 FROM CarPartsCTE
100
101 /*
102
103 注意看最下层的small_Mirror 位于 表最后的位置，
104
105 由此可以看出改递归不是开始就进行递归查询而是在1层完全展开后在根据该层展开下一层不是深度优先的递归
106
107 */
108
109 drop table CarParts
110
111 --------------------------------result---------------------------------------
112
113 CarID Part SubPart Qty
114
115 ----------- --------------- --------------- -----------
116
117 1 Body Door 4
118
119 1 Body Trunk Lid 1
120
121 1 Body Car Hood 1
122
123 1 Door Handle 1
124
125 1 Door Lock 1
126
127 1 Door Window 1
128
129 1 Body Rivets 1000
130
131 1 Door Rivets 100
132
133 1 Door Mirror 1
134
135 1 Mirror small_Mirror 4
136
137 (10 row(s) affected)
138
139 SubPart TotalNUM
140
141 --------------- -----------
142
143 Door 4
144
145 Trunk Lid 1
146
147 Car Hood 1
148
149 Rivets 1000
150
151 Handle 4
152
153 Lock 4
154
155 Window 4
156
157 Rivets 400
158
159 Mirror 4
160
161 small_Mirror 16
162
163 (10 row(s) affected)
164
165
示例：
以下示例显示经理以及向经理报告的雇员的层次列表。
代码
1 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
2
3 (
4
5 SELECT groupid, member, 0,type AS EmployeeLevel
6
7 FROM groupinfo
8
9 WHERE groupid = 'finance_company'
10
11 UNION ALL
12
13 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
14
15 FROM groupinfo e
16
17 INNER JOIN DirectReports d
18
19 ON e.groupid = d.member
20
21 )
22
23 SELECT b.nickname,groupid, member, EmployeeLevel,type
24
25 FROM DirectReports,userbasicinfo b
26
27 where DirectReports.member=b.id
28
29 and type = 1
30
31
最后顺带一个小提示：（很多人开始用的时候比较迷糊）
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
select n1,n2,n3,n4,max(n4) from table group by n1,n2,n3.
