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