相信明天会更好

• row_number()

用法如下：select  row_number() over(order by age) as [row number by age],FirstName,age from person其结果就是查出来的记录按照年龄进行了排序并且第一列为记录在结果集中的序号。如果不想按照年龄排序可以用如下语句来写SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],FirstName,AgeFROM Person。我们还可以先划分区间，然后在区间内进行排序，如：SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName,Age,Gender FROM Person，其结果就为先按照性别划分区间，同一个性别再按照年龄来排序例如：

• rank（）函数

select  rank() over(order by age) as [rank by age],firstname,age from person

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段了。

                                            转自：http://blog.csdn.net/duanzhi1984/archive/2008/10/24/3134831.aspx