http://wenku.baidu.com/view/89b12aea19e8b8f67c1cb93c
配
SQL2005中row_number( )、rank( )、dense_rank( )、ntile( )函数的用法
- 博客分类：
- Database
http://blog.youkuaiyun.com/htl258/article/details/4006668
SQL Server 2005 引入几个新的排序(排名)函数,如ROW_NUMBER、RANK、DENSE_RANK等。
这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。
-- ------------------------------------------------------------------------
ROW_NUMBER()
说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] < order_by_clause > ) 。
备注： ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数： < partition_by_clause > ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
返回类型： bigint 。
示例：
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER ( ORDER BY SalesYTD DESC ) AS ' Row Number ' , s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
USE AdventureWorks;
GO
WITH OrderedOrders AS
( SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER ( order by OrderDate) as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60 ;
-- ------------------------------------------------------------
RANK()
说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
参数： < partition_by_clause > ：将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
返回类型： bigint
示例：
-- 接上.
-- -----------------------------------------------------------------------------------
DENSE_RANK()
说明：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法：DENSE_RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
参数： < partition_by_clause > ：将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。
返回类型： bigint
示例：
USE AdventureWorks;
GO
SELECT
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
将上面三个函数放在一起计算，更能明显看出各个函数的功能。
CREATE TABLE rankorder(orderid INT ,qty INT )
INSERT rankorder VALUES ( 30001 , 10 )
INSERT rankorder VALUES ( 10001 , 10 )
INSERT rankorder VALUES ( 10006 , 10 )
INSERT rankorder VALUES ( 40005 , 10 )
INSERT rankorder VALUES ( 30003 , 15 )
INSERT rankorder VALUES ( 30004 , 20 )
INSERT rankorder VALUES ( 20002 , 20 )
INSERT rankorder VALUES ( 20001 , 20 )
INSERT rankorder VALUES ( 10005 , 30 )
INSERT rankorder VALUES ( 30007 , 30 )
INSERT rankorder VALUES ( 40001 , 40 )
INSERT rankorder VALUES ( 30007 , 30 )
GO
-- 对一个列qty进行的排序
SELECT orderid,qty,
FROM rankorder
ORDER BY qty
-- 对两个列qty,orderid进行的排序
SELECT orderid,qty,
FROM rankorder
ORDER BY qty,orderid
drop table rankorder
-- 示例数据
CREATE TABLE tb(Name varchar ( 10 ),Score decimal ( 10 , 2 ))
INSERT tb SELECT ' aa ' , 99
UNION ALL SELECT ' bb ' , 56
UNION ALL SELECT ' cc ' , 56
UNION ALL SELECT ' dd ' , 77
UNION ALL SELECT ' ee ' , 78
UNION ALL SELECT ' ff ' , 76
UNION ALL SELECT ' gg ' , 78
UNION ALL SELECT ' ff ' , 50
GO
-- 1. 名次生成方式1,Score重复时合并名次
SELECT * ,Place = ( SELECT COUNT ( DISTINCT Score) FROM tb WHERE Score >= a.Score)
FROM tb a
ORDER BY Place
-- 2. 名次生成方式2,Score重复时保留名次空缺
SELECT * ,Place = ( SELECT COUNT (Score) FROM tb WHERE Score > a.Score) + 1
FROM tb a
ORDER BY Place
create table cj(bj int ,zf int )
insert into cj select 1 , 98
insert into cj select 2 , 97
insert into cj select 1 , 96
insert into cj select 2 , 96
insert into cj select 1 , 95
insert into cj select 2 , 94
insert into cj select 1 , 94
insert into cj select 2 , 94
insert into cj select 1 , 93
select bj,
from cj a
go
drop table cj
SQL server 2005新增的几个函数,分别是row_number( ) 、rank( )、 ,DENSE_RANK( ) 、ntile( ) 下面以实例分别简单讲解。
1.row_number( )
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')
FirstName,
Age
FROM Person
出现的数据如下
Row Number by Age
--------------------------
1
2
3
4
5
6
7
8
9
10
11
可以观察到,是根据年龄升序排列了，并且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,
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注意,姓名M开始,序号又从1,2,3开始了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
2. RANK( ) 函数先看例子 SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], FirstName, Age FROM Person
输出如下: Rank by AgeFirstName 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 GenderFirstName 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( ) 函数
输出结果为:
Dense Rank by Age
-------------------- ---------- -----------
1
2
2
3
3
4
5
5
6
7
8
看到了么,和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
---------- ----------- --------------------
Larry
Doris
George
Mary
Sherry
Sam
Ted
Marty
Sue
Frank
John
这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段了。
排名函数是 SQL Server2005新加的功能。在 SQL Server2005中有如下四个排名函数：
1.
2.
3.
4.
图2
图3
as
(
)
select
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }
图4
图5
二、 rank
图6
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }
图7
三、 dense_rank
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }
图8
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }
图9
1.
2.
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
