ROW_NUMBER 用法小结
ROW_NUMBER函数是返回结果集分区内行的***,每个分区的第一行从 1 开始。
CREATE TABLE [dbo].[tb_Seller](
[编号] [nvarchar](50) NULL,
[商品名称] [nvarchar](50) NULL,
[销售额] [money] NULL,
[利润] [money] NULL,
[门店名称] [nvarchar](50) NULL,
[日期] [smalldatetime] NULL,
[销售员] [nvarchar](50) NULL
)
例如创建一个销售表,insert如下数据
1,按照销售额大小,加一个序号
select 编号,商品名称,销售额,利润,门店名称,日期,销售员,row_number() over (order by 销售额) as [序号]
from dbo.tb_Seller
2,返回特定的子集,例如我只要销售额第三名的
;with data as
(select 编号,商品名称,销售额,利润,门店名称,日期,销售员,row_number() over (order by 销售额) as [序号]
from dbo.tb_Seller)
select * from data where [序号]=3
3,还有一种在处理批量数据的时候,insert 了重复多余的数据,只是insert的时间不同,对于批量删除多余的数据就可以用到ROW_NUMBER,partition by。如下图,将该销售表的数据复制三分,只是时间不相同而已
用如下方法就可以很容易的删除掉多余的数据
--row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
--表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
;with data as
(select 编号,row_number() over (partition by convert(varchar(10),日期,120) order by 销售额) as [序号]
from dbo.tb_Seller group by 编号,销售额,日期)
delete a from dbo.tb_Seller a inner join data b on a.[编号]=b.[编号] where [序号] in (2,3)
以下排序函数DENSE_RANK、RANK、NTILE 和row_number类似都可以和over结合使用,其它排序函数的用法可以以后详细了解下。over函数还可以和sum,max 等聚合函数一起使用,max() over(partition by ... order by ...):求分组后的最大值。大家可以在SQL server 帮助,搜索中查找个函数的用法。