DENSE_RANK() 返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。 如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。ROW_NUMBER() 返回结果集分区内行的序列号,每个分区的第一行从开始。 ORDER BY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。NTILE() 将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。 */--创建测试数据表 declare @table table (姓名varchar(4),武力int) insert into @table select '吕布',108 union all select '赵云',107 union all select '马超',107 union all select '典韦',106 union all select '关羽',106 union all select '张飞',105 union all select '许褚',104 union all select '黄忠',103 union all select '颜良',100 union all select '文丑',100 union all select '庞德',99 union all select '孙策',99--查看一下 select * from @table/* 姓名 武力 ---- ----------- 吕布 108 赵云 107 马超 107 典韦 106 关羽 106 张飞 105 许褚 104 黄忠 103 颜良 100 文丑 100 庞德 99 孙策 99 */select 姓名,武力, row_number() over (order by 武力desc) as [row_number], rank() over (order by 武力desc) as [rank], dense_rank() over (order by 武力desc) as [dense_rank] from @table/* 姓名 武力 row_number rank dense_rank ---- ----------- -------------------- -------------------- -------------------- 吕布 108 1 1 1 赵云 107 2 2 2 马超 107 3 2 2 典韦 106 4 4 3 关羽 106 5 4 3 张飞 105 6 6 4 许褚 104 7 7 5 黄忠 103 8 8 6 颜良 100 9 9 7 文丑 100 10 9 7 庞德 99 11 11 8 孙策 99 12 11 8 */--博客地址:http://blog.csdn.net/maco_wang --NTILE() select 姓名,武力, NTILE(3) over (order by 武力desc) as [NTILE] from @table /* 姓名 武力 NTILE ---- ----------- -------------------- 吕布 108 1 赵云 107 1 马超 107 1 典韦 106 1 关羽 106 2 张飞 105 2 许褚 104 2 黄忠 103 2 颜良 100 3 文丑 100 3 庞德 99 3 孙策 99 3 */
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 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段了。
access中没有over
oracle和mysql中不了解。
好像是SQL Server 2005 later和Oracle才有效吧
一般他都是和其他的函数组合使用
具体看下MSDN吧
http://msdn.microsoft.com/en-us/library/ms189461.aspx
SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。
窗口函数功能非常强大,使用起来也十分容易。可以使用这个技巧立即得到大量统计值。
窗口是用户指定的一组行。 开窗函数计算从窗口派生的结果集中各行的值。2.适用范围:
排名开窗函数和聚合开窗函数.
也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用
OVER子句前面必须是排名函数或者是聚合函数3.例题:
--建立订单表
create table SalesOrder(
OrderID int, --订单id
OrderQty decimal(18,2) --数量
)
go--插入数据
insert into SalesOrder
select 1,2.0
union all
select 1,1.0
union all
select 1,3.0
union all
select 2,6.0
union all
select 2,1.1
union all
select 3,8.0
union all
select 3,1.1
union all
select 3,7.0
go--查询得如下结果
select * from SalesOrder
goOrderID OrderQty
----------- ------------
1 2.00
1 1.00
1 3.00
2 6.00
2 1.10
3 8.00
3 1.10
3 7.00
复制代码
现要求显示汇总总数,每当所占比例,分组汇总数,每单在各组所占比例,要求格式如下:OrderID OrderQty 汇总 每单比例 分组汇总 每单在各组比例
1 2.00 29.20 0.0685 6.00 0.3333
1 1.00 29.20 0.0342 6.00 0.1667
1 3.00 29.20 0.1027 6.00 0.5000
2 6.00 29.20 0.2055 7.10 0.8451
2 1.10 29.20 0.0377 7.10 0.1549
3 8.00 29.20 0.2740 16.10 0.4969
3 1.10 29.20 0.0377 16.10 0.0683
3 7.00 29.20 0.2397 16.10 0.4348
--利用窗口函数和聚合开窗函数,可以很快实现上述要求
select OrderID,OrderQty,
sum(OrderQty) over() as [汇总],
convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每单所占比例],
sum(OrderQty) over(PARTITION BY OrderID) as [分组汇总],
convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每单在各组所占比例]
from SalesOrder
order by OrderID
复制代码
窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果:
sql2000的实现步骤较麻烦,先计算出总数,再分组计算汇总,最后连接得到结果
--sql2000
declare @sum decimal(18,2)
select @sum=sum(OrderQty)
from SalesOrder--按OrderID,计算每组的总计,然后插入临时表
select OrderID,sum(OrderQty) as su
into #t
from SalesOrder
group by OrderID--连接临时表,得到结果
select s.OrderID,s.OrderQty,
@sum as [汇总],
convert(decimal(18,4),s.OrderQty/@sum) as [每单所占比例],
t.su as [分组汇总],
convert(decimal(18,4),s.OrderQty/t.su) as [每单在各组所占比例]
from SalesOrder s join #t t
on t.OrderID=s.OrderID
order by s.OrderIDdrop table #t
go
复制代码
上面演示的都是窗口函数与聚合开窗函数的使用,它与排名开窗函数请看下面例题:
--与排名开窗函数使用
select OrderID,OrderQty,
rank() over(PARTITION BY orderid order by OrderQty ) as [分组排名],
rank() over(order by OrderQty ) as [排名]
from SalesOrder
order by orderid asc--查询得如下结果
OrderID OrderQty 分组排名 排名
1 2.00 2 4
1 3.00 3 5
1 1.00 1 1
2 1.10 1 2
2 6.00 2 6
3 7.00 2 7
3 8.00 3 8
3 1.10 1 2
复制代码
-- =============================================
-- Title: 实例说明四个排名函数的区别
-- Author: maco_wang(叶子)
-- Create date: 2011-04-26
-- Description: 三国版
-- =============================================
/*
RANK()
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
DENSE_RANK()
返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。ROW_NUMBER()
返回结果集分区内行的序列号,每个分区的第一行从开始。
ORDER BY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。NTILE()
将有序分区中的行分发到指定数目的组中。
各个组有编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。
*/--创建测试数据表
declare @table table (姓名varchar(4),武力int)
insert into @table
select '吕布',108 union all
select '赵云',107 union all
select '马超',107 union all
select '典韦',106 union all
select '关羽',106 union all
select '张飞',105 union all
select '许褚',104 union all
select '黄忠',103 union all
select '颜良',100 union all
select '文丑',100 union all
select '庞德',99 union all
select '孙策',99--查看一下
select * from @table/*
姓名 武力
---- -----------
吕布 108
赵云 107
马超 107
典韦 106
关羽 106
张飞 105
许褚 104
黄忠 103
颜良 100
文丑 100
庞德 99
孙策 99
*/select 姓名,武力,
row_number() over (order by 武力desc) as [row_number],
rank() over (order by 武力desc) as [rank],
dense_rank() over (order by 武力desc) as [dense_rank]
from @table/*
姓名 武力 row_number rank dense_rank
---- ----------- -------------------- -------------------- --------------------
吕布 108 1 1 1
赵云 107 2 2 2
马超 107 3 2 2
典韦 106 4 4 3
关羽 106 5 4 3
张飞 105 6 6 4
许褚 104 7 7 5
黄忠 103 8 8 6
颜良 100 9 9 7
文丑 100 10 9 7
庞德 99 11 11 8
孙策 99 12 11 8
*/--博客地址:http://blog.csdn.net/maco_wang
--NTILE()
select 姓名,武力,
NTILE(3) over (order by 武力desc) as [NTILE]
from @table
/*
姓名 武力 NTILE
---- ----------- --------------------
吕布 108 1
赵云 107 1
马超 107 1
典韦 106 1
关羽 106 2
张飞 105 2
许褚 104 2
黄忠 103 2
颜良 100 3
文丑 100 3
庞德 99 3
孙策 99 3
*/
先来点数据,先建个表 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 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段了。