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

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 Doris 6 F

2 Mary 11 F

3 Sherry 11 F

4 Sue 29 F

1 Larry 5 M

2 George 6 M

3 Sam 17 M

4 Ted 23 M

5 Marty 23 M

6 Frank 38 M

7 John 40 M注意,姓名M开始,序号又从1,2,3开始了

（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 Doris 6

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 Doris 6 F

2 Mary 11 F

2 Sherry 11 F

4 Sue 29 F

1 Larry 5 M

2 George 6 M

3 Sam 17 M

4 Ted 23 M

4 Marty 23 M

6 Frank 38 M

7 John 40 M

可以看到,按性别分组了,每个性别分组里,继续是用了rank( )函数

（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 Doris 6

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

Doris 6 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)

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-941A-768D7A8CCB6C 108000
52A3CE16-74FD-4D5D-BB4F-F5F67A1E9D2F 120000
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.

有错误的地方欢迎大家拍砖,希望交流和共享。