SQL SERVER2005中新增4种排名函数-白红宇

SQL SERVER2005中新增4种排名函数

阅读量：5119 次

发布时间：2019-06-13

本文共 5192 字，大约阅读时间需要 17 分钟。

SQL server 2005新增的几个函数,分别是row_number( )、rank( )、,DENSE_RANK( )、ntile( )下面以实例分别简单讲解。

1.row_number( )

先来点数据,先建个表

SET NOCOUNT ON

CREATE TABLE Person(

FirstName VARCHAR(10),

Age INT,

Gender CHAR(1))

INSERT INTO Person VALUES ('Ted',23,'M')

INSERT INTO Person VALUES ('John',40,'M')

INSERT INTO Person VALUES ('George',6,'M')

INSERT INTO Person VALUES ('Mary',11,'F')

INSERT INTO Person VALUES ('Sam',17,'M')

INSERT INTO Person VALUES ('Doris',6,'F')

INSERT INTO Person VALUES ('Frank',38,'M')

INSERT INTO Person VALUES ('Larry',5,'M')

INSERT INTO Person VALUES ('Sue',29,'F')

INSERT INTO Person VALUES ('Sherry',11,'F')

INSERT INTO Person VALUES ('Marty',23,'F')

直接用例子说明问题:

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 Age select * from #A drop table #a

如果不想按年龄排序,可以这样写 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,GenderFROM Person

这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下

Partition by Gender         FirstName         Age                Gender-------------------- ---------- ----------- ------1                           Doris             6                  F2                           Mary              11                 F3                           Sherry            11                 F4                           Sue               29                 F1                           Larry             5                  M2                           George            6                  M3                           Sam               17                 M4                           Ted               23                 M5                           Marty             23                 M6                           Frank             38                 M7                           John              40                 M

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

2.RANK( )函数          先看例子 SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],FirstName,AgeFROM Person

输出如下: Rank by Age                 FirstName         Age-------------------- ---------- -----------1                           Larry             52                           Doris             62                           George            64                           Mary              114                           Sherry            116                           Sam               177                           Ted               237                           Marty             239                           Sue               2910                          Frank             3811                          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                  F2                           Mary              11                 F2                           Sherry            11                 F4                           Sue               29                 F1                           Larry             5                  M2                           George            6                  M3                           Sam               17                 M4                           Ted               23                 M4                           Marty             23                 M6                           Frank             38                 M7                           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