SQL ROW_NUMBER() OVER函数的基本用法.doc

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。示例：xlh row_num1700 11500 21085 3710 4row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)实例：初始化数据create table employee (empid int ,deptid int ,salary decimal(10,2)insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into employee values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)数据显示为empid deptid salary- - -1 10 5500.002 10 4500.003 20 1900.004 20 4800.005 40 6500.006 40 14500.007 40 44500.008 50 6500.009 50 7500.00需求：根据部门分组，显示每个部门的工资等级预期结果：empid deptid salary rank- - - -1 10 5500.00 12 10 4500.00 24 20 4800.00 13 20 1900.00 27 40 44500.00 16 40 14500.00 25 40 6500.00 39 50 7500.00 18 50 6500.00 2SQL脚本：SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。示例：xlh row_num1700 11500 21085 3710 4row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)实例：初始化数据create table employee (empid int ,deptid int ,salary decimal(10,2)insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into employee values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)数据显示为empid deptid salary- - -1 10 5500.002 10 4500.003 20 1900.004 20 4800.005 40 6500.006 40 14500.007 40 44500.008 50 6500.009 50 7500.00需求：根据部门分组，显示每个部门的工资等级预期结果：empid deptid salary rank- - - -1 10 5500.00 12 10 4500.00 24 20 4800.00 13 20 1900.00 27 40 44500.00 16 40 14500.00 25 40 6500.00 39 50 7500.00 18 50 6500.00 2SQL脚本：SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employeesql语句分页多种方式ROW_NUMBER()OVER方式一select top pageSize * from company where id not in(select top pageSize*(pageIndex-1) id from company)方式二ROW_NUMBER()OVER-ROW_NUMBER() 就是生成一个有顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ID)-还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。select getdate()select * from company where id in (-搜索出settable表中所有的编号，也就是company表中的id，这里只不过要得到num（即有顺序的编号）select id from-搜索出出表中的所有的id，并且新建一列num用来存取排序的编号，并且把这张表赋值给settable(select id,row_number() over (order by id) asnum from company)as settable-添加搜索条件页索引和页大小where num between (pageIndex-1)*pageSize+1 and pageIndex*pageSize)select getdate()方式三SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id asc) AS rownum,idFROM company ) AS DWHERE rownum BETWEEN (pageIndex-1)*pageSize+1 AND pageIndex*pageSizeORDER BY id ascSql Server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在Sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多. 我们先来看看ROW_NUMBER()是干什么的.执行下面这段SQL语句: SELECT ReportID,UserName, ReportID, TimeStart, TimeEnd,ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo FROM ExecutionLog很简单,ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个RowNo进行分页. 现在,假设我每一页的数据是10条,我们就可以使用如下所示的SQL语句返回指定页的数据: SELECT TOP 10 * FROM ( SELECT top 10 InstanceName, UserName, ReportID, TimeStart, TimeEnd,ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo FROM ExecutionLog ) AS A WHERE RowNo + pageIndex*10 pageIndex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.=下面举个例子:ROW_NUMBER函数SQL Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前，你不得不作棘手的事，像创建一个有序号列的临时表，然后把你的SELECT结果插入到这个临时表中。现在，用ROW_NUMBER函数，你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号，你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS Row Number, * fromdbo.employee结果Row Number employee_id Firstname Lastname soc_sec1 5623222 Tim Jones 123-65-87452 5632111 Rob Kinkad 456-69-87543 6365666 Jim Miller 236-56-89894 7563333 Joe Roberts 564-89-5555这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。OVER语句使SQL Server基于employee_id列增加行序号。换句话说，产生了行序号，就好像数据按employee_id做了排序。这是很重要的一点，因为你仍然可以改变SELECT的排序顺序。以下面的查询为例：SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS Row Number, * fromdbo.employeeORDER BY soc_sec结果Row Number employee_id Firstname Lastname soc_sec1 5623222 Tim Jones 123-65-87453 6365666 Jim Miller 236-56-89892 5632111 Rob Kinkad 456-69-87544 7563333 Joe Roberts 564-89-5555注意第二个结果集数据是按社会安全编号来排序的，但是行号仍然创建得好像数据是按employee_id排序的。-分页存储过程IF EXISTS (SELECT * FROM sysobjects where name=P_student)DROP PROCEDURE P_studentgoCREATE PROCEDURE P_studentstartIndex INT, pageSize INTASbegin WITH studentList AS (SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row,O.stuid,O.stuname,O.stuage,O.stuinfofrom student O)SELECT Row, stuid,stuname,stuage,stuinfoFROM studentListWHERE Row between startIndex and startIndex+pageSize-1end-分页2-set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE dbo.Deer_Page(startIndex INT,pageSize INT,strSql varchar(5000) -查询条件,TableName varchar(50),DoCount AS bit=1 - 0值返回记录总数, 非 0 值则返回记录)ASbegin tranIF DoCount=0 Goto GetCountElse Goto GetSearchGetCount: -返回记录总数 DECLARE SearchSql AS Nvarchar(4000) SET SearchSql= SELECT Count(*) AS Total FROM +TableName+ WHERE IntReserve1=0 exec sp_executesql SearchSql -print SearchSqlCOMMIT TRAN returnGetSearch: -返回记录DECLARE SqlQuery varchar(4000)SET SqlQuery=SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from +TableName+ O Where IntReserve1=0) as temp WHERE Row BETWEEN +cast(startIndex as varchar) + and +cast(startIndex+pageSize-1 as varchar)+ strsql-print SqlQueryexecute(SqlQuery)COMMIT TRANsql ROW_NUMBER() 排序函数1使用row_number（）函数进行编号：如View Code 1select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer原理：先按psd进行排序，排序完后，给每条数据进行编号。2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：View Code 1select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order3.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了。如图：代码如下：View Code 1select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order4.统计每一个客户最近下的订单是第几次下的订单。代码如下：View Code 1with tabs as2(3select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order4)56select MAX(rows) as 下单次数,customerID from tabs group by customerID5.统计每一个客户所有的订单中购买的金额最小，而且并统计改订单中，客户是第几次购买的。如图：上图：rows表示客户是第几次购买。思路：利用临时表来执行这一操作1.先按客户进行分组，然后按客户的下单的时间进行排序，并进行编号。2.然后利用子查询查找出每一个客户购买时的最小价格。3.根据查找出每一个客户的最小价格来查找相应的记录。代码如下：