--2005使用row_number() over()窗口函数

Question

2005我因为做的网站很多只能用sql2000，2005中的cte，row_number() over()，都不懂，以后求职麻烦么？
就是新增加的功能难么？
对于提问，大家谈谈row_number()即可

Answer 1

如果拿数据库前20条内容是select top 20 from tab
如果拿20-40这20条内容怎么办？--2005使用row_number() over()窗口函数select *
from (select *,rowid=row_number() over(order by 主键) from tb) as t
where rowid > 20 and rowid <= 30

Answer 2

ROW_NUMBER() 
回结果集分区内行的序列号，每个分区的第一行从 1 开始。 
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。 

Answer 3

/*
标题：ROW_NUMBER、RANK、DENSE_RANK的用法
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2007-12-16
地点：广东深圳
*/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 函数的分区。 
      <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。示例：
/*以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/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
/*
FirstName  LastName    Row Number  SalesYTD      PostalCode
---------  ----------  ----------  ------------  ----------------------------
Shelley    Dyck        1           5200475.2313  98027
Gail       Erickson    2           5015682.3752  98055
Maciej     Dusza       3           4557045.0459  98027
Linda      Ecoffey     4           3857163.6332  98027
Mark       Erickson    5           3827950.238   98055
Terry      Eminhizer   6           3587378.4257  98055
Michael    Emanuel     7           3189356.2465  98055
Jauna      Elson       8           3018725.4858  98055
Carol      Elliott     9           2811012.7151  98027
Janeth     Esteves     10          2241204.0424  98055
Martha     Espinoza    11          1931620.1835  98055
Carla      Eldridge    12          1764938.9859  98027
Twanna     Evans       13          1758385.926   98055
(13 行受影响)
*/
 
/*以下示例将返回行号为 50 到 60（含）的行，并以 OrderDate 排序。*/ 
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;
/*
SalesOrderID OrderDate               RowNumber
------------ ----------------------- --------------------
43708        2001-07-03 00:00:00.000 50
43709        2001-07-03 00:00:00.000 51
43710        2001-07-03 00:00:00.000 52
43711        2001-07-04 00:00:00.000 53
43712        2001-07-04 00:00:00.000 54
43713        2001-07-05 00:00:00.000 55
43714        2001-07-05 00:00:00.000 56
43715        2001-07-05 00:00:00.000 57
43716        2001-07-05 00:00:00.000 58
43717        2001-07-05 00:00:00.000 59
43718        2001-07-06 00:00:00.000 60
(11 行受影响)
*/--------------------------------------------------------------
RANK()说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
      例如，如果两位顶尖销售员具有同样的 SalesYTD 值，他们将并列第一。
      由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。
      因此，RANK 函数并不总返回连续整数。 
      用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数：< partition_by_clause > ：将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。 
      < order_by_clause >：确定将 RANK 值应用于分区中的行时所基于的顺序。
返回类型：bigint示例：
/*以下示例按照数量对清单中的产品进行了排名。行集按 LocationID 分区，按 Quantity 排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p 
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
/*
ProductID   Name                                               LocationID Quantity RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1           Adjustable Race                                    6          324      71
1           Adjustable Race                                    1          408      78
1           Adjustable Race                                    50         353      117
2           Bearing Ball                                       6          318      67
2           Bearing Ball                                       1          427      85
2           Bearing Ball                                       50         364      122
3           BB Ball Bearing                                    50         324      106
3           BB Ball Bearing                                    1          585      110
3           BB Ball Bearing                                    6          443      115
4           Headset Ball Bearings                              1          512      99
4           Headset Ball Bearings                              6          422      108
4           Headset Ball Bearings                              50         388      140
316         Blade                                              10         388      33
......
(1069 行受影响)
*/ -------------------------------------------------------------------------------------
DENSE_RANK()说明：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法：DENSE_RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注：如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
      例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。
      接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。
      因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。 
      整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
参数：< partition_by_clause > ：将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。 
      < order_by_clause >：确定将 DENSE_RANK 值应用于分区中各行的顺序。
返回类型：bigint示例：
/*以下示例返回各位置上产品数量的 DENSE_RANK。 */
USE AdventureWorks;
GO
SELECT  i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
/*
ProductID   Name                                               LocationID Quantity DENSE_RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1           Adjustable Race                                    1          408      57
1           Adjustable Race                                    6          324      52
1           Adjustable Race                                    50         353      82
879         All-Purpose Bike Stand                             7          144      34
712         AWC Logo Cap                                       7          288      38
3           BB Ball Bearing                                    50         324      74
3           BB Ball Bearing                                    6          443      81
3           BB Ball Bearing                                    1          585      82
*/-------------------------------------------------------------------------------------------------------
将上面三个函数放在一起计算，更能明显看出各个函数的功能。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,
       ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
       RANK()       OVER(ORDER BY qty) AS rank,
       DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty
/*
orderid     qty         rownumber            rank                 denserank
----------- ----------- -------------------- -------------------- --------------------
30001       10          1                    1                    1
10001       10          2                    1                    1
10006       10          3                    1                    1
40005       10          4                    1                    1
30003       15          5                    5                    2
30004       20          6                    6                    3
20002       20          7                    6                    3
20001       20          8                    6                    3
10005       30          9                    9                    4
30007       30          10                   9                    4
30007       30          11                   9                    4
40001       40          12                   12                   5
(12 行受影响)
*/ --对两个列qty,orderid进行的排序
SELECT orderid,qty,
       ROW_NUMBER() OVER(ORDER BY qty,orderid) AS rownumber,
       RANK()       OVER(ORDER BY qty,orderid) AS rank,
       DENSE_RANK() OVER(ORDER BY qty,orderid) AS denserank
FROM rankorder
ORDER BY qty,orderid
drop table rankorder
/*
orderid     qty         rownumber            rank                 denserank
----------- ----------- -------------------- -------------------- --------------------
10001       10          1                    1                    1
10006       10          2                    2                    2
30001       10          3                    3                    3
40005       10          4                    4                    4
30003       15          5                    5                    5
20001       20          6                    6                    6
20002       20          7                    7                    7
30004       20          8                    8                    8
10005       30          9                    9                    9
30007       30          10                   10                   10
30007       30          11                   10                   10
40001       40          12                   12                   11
(12 行受影响)
*/

Answer 4

用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。 
CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。 
与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。 
CTE可用于： 
1.创建递归查询(我个人认为CTE最好用的地方) 
2.在同一语句中多次引用生成的表 
CTE优点: 
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。 
查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。 
CTE可使用的范围： 
可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。 
下面看一个简单的CTE例题： 
把test表中salary最大的id记录保存在test_CTE中,再调用 
复制代码 代码如下:
with test_CTE(id,salary) 
as 
( 
select id ,max(salary) 
from test 
group by id 
) 
select * from test_cte 由上面例题可以看出： 
CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。 
定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。 
简单的说CTE可以替代临时表和表变量的功能。 
我个人认为cte最好用的地方是创建递归查询，下面演示一下这功能： 
现有一数据结构如下： 这些数据存放在表Co_ItemNameSet中,表结构和部分数据如下： 
ItemId ParentItemId ItemName 
2 0 管理费用 
3 0 销售费用 
4 0 财务费用 
5 0 生产成本 
35 5 材料 
36 5 人工 
37 5 制造费用 
38 35 原材料 
39 35 主要材料 
40 35 间辅材料 
41 36 工资 
42 36 福利 
43 36 年奖金 
现在需求是:我想查询ItemId=2,也就是管理费用和其下属所有节点的信息 
通过CTE可以很简单达到需求要的数据 
为了体现CTE的方便性，我特意也写了一个sql2000版本的解决方法，先看看sql2000是怎么解决这个问题的 
复制代码 代码如下:
--sql2000版本 
DECLARE @i INT 
SELECT @i=2; 
/* 
使用临时表作为堆栈来跟踪所有正在处理中的项目（已经开始但尚未结束）。 
某个项目一旦处理完毕，将被从堆栈中删除。 
当发现新的项目时，这些项目将被添加到堆栈中。 
*/ 
CREATE TABLE #tem( 
[ItemId] [INT] NOT NULL, 
[level] INT 
); 
/* 
存放结果 
*/ 
CREATE TABLE #list( 
[ItemId] [INT] NOT NULL, 
[ParentItemId] [INT] NOT NULL DEFAULT ((0)), 
[ItemName] [nvarchar](100) NOT NULL DEFAULT (''), 
[level] INT 
); 
INSERT INTO #tem([ItemId],[level]) 
SELECT ItemId, 1 
FROM Co_ItemNameSet 
WHERE itemid=@i 
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level]) 
SELECT ItemId, ParentItemId, ItemName ,1 
FROM Co_ItemNameSet 
WHERE itemid=@i 
DECLARE @level INT 
SELECT @level=1 
DECLARE @current INT 
SELECT @current=0 
/* 
当 @level 大于 0 时，执行以下步骤： 
1.如果当前级别 (@level) 的堆栈中有项目，就选择其中一个，并称之为 @current。 
2.从堆栈中删除该项目以免重复处理它，然后将其所有子项目添加到堆栈的下一级 (@level + 1) 中。 
3.如果有子项目 (IF @@ROWCOUNT > 0)，则下降一级处理它们 (@level = @level + 1)；否则，继续在当前级别上处理。 
4.最后，如果在当前级别的堆栈中没有待处理的项目，则返回到上一级，看上一级是否有待处理的项目 (@level = @level - 1)。当再没有上一级时，则完毕。 
*/ 
WHILE(@level>0) 
BEGIN 
SELECT @current=ItemId 
FROM #tem 
WHERE [level]=@level 
IF @@ROWCOUNT>0 
BEGIN 
--从堆栈中删除该项目以免重复处理它 
DELETE FROM #tem 
WHERE [level]=@level and ItemId=@current 
--将其所有子项目添加到堆栈的下一级 (@level + 1) 中。 
INSERT INTO #tem([ItemId],[level]) 
SELECT [ItemId],@level+1 
FROM Co_ItemNameSet 
WHERE ParentItemId=@current 
--将其所有子项目添加 
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level]) 
SELECT [ItemId],[ParentItemId],[ItemName] ,@level+1 
FROM Co_ItemNameSet 
WHERE ParentItemId=@current 
IF @@rowcount>0 
BEGIN 
SELECT @level=@level+1 
END 
END 
ELSE 
BEGIN 
SELECT @level=@level-1 
END 
END 
--显示结果 
SELECT * FROM #list 
DROP TABLE #tem 
DROP TABLE #list 
go 结果如下： 
ItemId ParentItemId ItemName level 
2 0 管理费用 1 
52 2 汽车费用 2 
55 2 招聘费 2 
56 2 排污费 2 
53 52 燃料 3 
54 52 轮胎 3 
大家看到sql2000解决这个问题比较麻烦，要实现这需求编写的代码比较多，比较复杂 
现在好了，在sql2005中通过CTE的递归特点可以2步就实现. 
得到同样的结果，sql2005的CTE代码简单了许多.这就是CTE支持递归查询的魅力。 
请看下面的代码： 
复制代码 代码如下:
--sql2005版本 
DECLARE @i INT 
SELECT @i=2; 
WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level) 
AS 
( 
SELECT ItemId, ParentItemId, ItemName ,1 AS [Level] 
FROM Co_ItemNameSet 
WHERE itemid=@i 
UNION ALL 
SELECT c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1 
FROM Co_ItemNameSet c INNER JOIN Co_ItemNameSet_CTE ct 
ON c.ParentItemId=ct.ItemId 
) 
SELECT * FROM Co_ItemNameSet_CTE 
go 

Answer 5

Sql Server2005 4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE() 
下面是对这4个函数的解释：
RANK() 
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
例如，如果两位销售员具有相同的SalesYTD值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD的销售人员将排名第三。
因此，RANK 函数并不总返回连续整数。
DENSE_RANK()
返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。
因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。 
ROW_NUMBER()
回结果集分区内行的序列号，每个分区的第一行从 1 开始。
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
NTILE()
将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。
如果分区的行数不能被 integer_expression 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。
例如，如果总行数是 53，组数是 5，则前三个组每组包含 11 行，其余两个组每组包含 10 行。
另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。
例如，如果总行数为 50，有五个组，则每组将包含 10 行。
--演示例题，建一个table
create table rankorder(
orderid int,
qty int
)
go
--插入数据
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
--查询出各类排名
SELECT orderid,qty,
  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
  RANK()       OVER(ORDER BY qty) AS [rank],
  DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
  NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty--结果
--ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续
--RANK()是按qty由小到大逐一排名，并列，排名不连续
--DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续
--NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续
orderid    qty    rownumber    rank    denserank    NTILE
30        10    1            1        1            1
10        10    2            1        1            1
80        10    3            1        1            1
40        10    4            1        1            1
30        15    5            5        2            2
30        20    6            6        3            2
22        20    7            6        3            2
21        20    8            6        3            2
10        30    9            9        4            3
30        30    10            9        4            3
40        40    11            11        5            3
sql 2005实现排名非常方便，但是用sql 2000实现排名就比较麻烦，下面是sql 2000的实现代码:--RANK在sql 2000中的实现
select orderid,qty,
    (select count(1)+1 from rankorder where qty<r.qty) as [rank]
from rankorder r
ORDER BY qty
go
 
--ROW_NUMBER在sql 2000中的实现
--利用临时表和IDENTITY（函数）
select identity(int,1,1) as [ROW_NUMBER],orderid,qty
into #tem             
from rankorderselect orderid,qty,[ROW_NUMBER]
from #temdrop table #tem
go--DENSE_RANK在sql 2000中的实现
select identity(int,1,1) as ids,  qty
into #t
from rankorder
group by qty
order by qtyselect r.orderid,r.qty,t.ids as [DENSE_RANK]
from rankorder r join #t  t
on r.qty=t.qtydrop table #t
go排名函数是与窗口函数OVER()配合一起使用的。
如果借助OVER子句的参数PARTITION BY,就可以将结果集分为多个分区。排名函数将在每个分区内进行排名.--例题
SELECT orderid,qty,
DENSE_RANK() OVER(ORDER BY qty) AS a ,
DENSE_RANK() OVER(PARTITION BY orderid ORDER BY qty) AS b     
FROM rankorder
ORDER BY qty--说明：
--a列是在全部记录上进行的排名
--b列是把orderid中的记录分成了10,21,22,30,40,80这6个区，再在每个区上进行的排名。
orderid    qty    a    b
10        10    1    1
30        10    1    1
40        10    1    1
80        10    1    1
30        15    2    2
30        20    3    3
21        20    3    1
22        20    3    1
10        30    4    2
30        30    4    4
40        40    5    2
我们看到排名函数可以很简便的得到各种类型的排名
以下是我对4个排名函数的类比表格：
                               排名连续性   排名并列性
RANK()                 不一定连续    有并列
DENSE_RANK()    连续               有并列
ROW_NUMBER()   连续             无并列 
NTILE()                  连续               有并列

Answer 6

create table tb(id varchar(10),px int)
insert into tb values('1001',1)
insert into tb values('1009',1)
insert into tb values('1001',2)
insert into tb values('1009',2)
goselect *,new_px = row_number() over(order by id , px)  from tb
/*
id         new_px
---------- --------------------
1001       1
1001       2
1009       3
1009       4(4 行受影响)
*/select *,
       new_px1 = row_number() over(partition by id order by px ) , 
       new_px2 = row_number() over(partition by id order by px desc)  
from tb/*
id         px          new_px1              new_px2
---------- ----------- -------------------- --------------------
1001       2           2                    1
1001       1           1                    2
1009       2           2                    1
1009       1           1                    2(4 行受影响)*/drop table tb

Answer 7

--演示例题，建一个table 
create table tb( 
id int, 
qty int 
) 
go 
--插入数据 
insert tb values(30,10) 
insert tb values(10,10) 
insert tb values(80,10) 
insert tb values(40,10) 
insert tb values(30,15) 
insert tb values(30,20) 
insert tb values(22,20) 
insert tb values(21,20) 
insert tb values(10,30) 
insert tb values(30,30) 
insert tb values(40,40) 
go 
select id,qty,row=row_number() over(order by qty) from tb
/*id          qty         row
----------- ----------- --------------------
30          10          1
10          10          2
80          10          3
40          10          4
30          15          5
30          20          6
22          20          7
21          20          8
10          30          9
30          30          10
40          40          11(11 行受影响)
*/
--partition by 可选 将结果集分为多个分区。 开窗函数分别应用于每个分区，
--并为每个分区重新启动计算。
select id,qty,row=row_number() over(partition by id order by qty) from tb
/*
id          qty         row
----------- ----------- --------------------
10          10          1
10          30          2
21          20          1
22          20          1
30          10          1
30          15          2
30          20          3
30          30          4
40          10          1
40          40          2
80          10          1(11 行受影响)
*/
drop table tb