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

最新推荐文章于 2025-07-04 22:25:44 发布

原创最新推荐文章于 2025-07-04 22:25:44 发布 · 2.5w 阅读

10 ·

CC 4.0 BY-SA版权

sqlServer 专栏收录该内容

50 篇文章

订阅专栏

本文深入探讨了SQL中ROW_NUMBER()函数的应用，通过实例演示了如何使用该函数进行数据排序、分组排序，以及如何统计客户最新的订单次数和首次下单情况。涵盖了从基本排序到复杂业务场景的数据处理技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--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