SQL Server中row_number

简介：

SQL Server2005中增加四大排名函数（row_number, rank（名次会跳跃）, dense_rank（名次不跳跃），ntile）， over简称开窗函数，要想具体了解的可以参见：https://www.cnblogs.com/Brambling/p/6706992.html

这里介绍的只是介绍row_number()的用法

1 语法格式

row_number() over(partition by 分组列 order by 排序列 desc)

2 功能

简单的说row_number()从1开始，为每一条分组记录返回一个数字，同时over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行，

例如：row_number () over(partition by userId order by fee) 表示 按照userId分组， 每组按照fee降序排序，每组再为降序后记录返回一个序号，当出现一个组的fee相同时，两行返回的排序是不同的。rank() 函数当fee相同时排序是相同的。

3 具体例子功能说明

基本数据：

create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);

eg1：根据salary排序并返回序号

select id,name,age,salary,row_number() over(order by salary desc) RANK from TEST_ROW_NUMBER_OVER t

eg2：根据id分组且每组根据salary降序

select id,name,age,salary,row_number()over(partition by id order by salary desc) rank from TEST_ROW_NUMBER_OVER t

eg3：根据id分组且每组根据salary降序且找出每组中序号为1的数据

select * from (select id, name, age, salary , row_number() over(partition by id order by salary desc) rank from TEST_ROW_NUMBER_OVER t) where rank ==1 

eg4: 排序找出年龄在13岁到16岁数据，按salary排序

select id,name,age,salary,row_number()over(order by salary desc) rankfrom TEST_ROW_NUMBER_OVER t where age between '13' and '16'

结论：结果中 rank 的序号是连续的， 表明了 over(order by salary desc) 是在where age between and 后执行的

4  其他常用场景

eg1：使用row_number（）函数进行编号，原理：先按psd进行排序，排序完后，给每条数据进行编号。

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

eg2：在订单中按价格的升序进行排序，并给每条记录进行排序代码如下

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

eg3：统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了：

select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order

eg4：统计每一个客户最近下的订单是第几次下的订单：

with tab as

（select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order）

select max(rows) as "下单次数" ，coustomerID from tab group by coustomerID

eg5：统计每一个客户所有的订单中购买的金额最小，而且并统计改订单中，客户是第几次购买的

with tab as

（select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order）

select * from tabs

where totalPrice in

 (

     select min(totalPrice) from tabs group by customerID

)

eg6:筛选出客户第一次下的订单。

with tabs as  
    (  
    select ROW_NUMBER() over(partition by customerID  order by insDT) as rows,* from OP_Order  
    )  
    select * from tabs where rows = 1 
    select * from OP_Order