sqlserver ROW_NUMBER()OVER()详解和用法

--ROW_NUMBER()OVER(partition by 分组字段,order by 排序字段) 执行顺序要晚于 where 、group by 、order by 
--定义一个订单表
create table tb_order(
customerId nvarchar(50),
sDateTime nvarchar(50),
price decimal(18,3),
product nvarchar(50),
phoneNum nvarchar(50)
)

--插入订单数据
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('001','2019-06-01','0.5','pair','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('002','2019-06-01','0.5','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('002','2019-06-02','0.6','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('003','2019-06-01','0.5','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('003','2019-06-02','0.6','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('003','2019-06-03','0.7','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('004','2019-06-01','0.5','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('004','2019-06-02','0.6','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('004','2019-06-03','0.7','apple','15136257896')
insert into tb_order(customerId,sDateTime,price,product,phoneNum)values('004','2019-06-04','0.8','apple','15136257896')
--执行顺序:在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行
select * , ROW_NUMBER()OVER(order by price ) as rank  from tb_order where product ='apple'
--排序(按单价排序并编号)
select * ,ROW_NUMBER()OVER(order by price) from tb_order

--分组排序(统计每个客户的所有订单，并根据金额进行排序、编号)
select * ,ROW_NUMBER()OVER(partition  by customerId order by price)as rank  from tb_order

--统计客户最新的订单是第几次下的订单
 with temp as(
 select * ,ROW_NUMBER()OVER(partition by customerid order by sDatetime) as rank from tb_order
 ) select customerid,max(rank) from temp group by customerid;

 --统计客户第几次下单金额最小
with temp as (
select * ,ROW_NUMBER()OVER(partition by customerid order by sDatetime) as rank from tb_order 
)select   * from temp  where price in(select min(price) from tb_order group by customerid)

--统计客户第一次下的订单
with temp as (
select * ,ROW_NUMBER()OVER(partition by customerid order by sDatetime) as rank from tb_order 
)select * from temp where rank=1