在实际的项目开发中,我们经常使用分组函数,对组内数据进行群组后,然后进行组内排序:
如:
1:取出一个客户一段时间内,最大订单数的行记录
2: 取出一个客户一段时间内,最后一次销售记录的行记录
————————————————
下文将讲述三个分组函数 row_number rank dense_rank的用法 ,
以上三个函数的功能为:返回行数据在”分组数据内”的排列值
1:row_number() over() 函数简介
row_number() over(partition by [分组列] order by [排序列])
分组列:这里放入我们需要群组的列,可以为一列 也可以为多列,之间采用逗号分隔
排序列:分组后,排序依据列
通过row_number() over()排序后,依次生成分组后,行数据在分组内的排序值(1,2,3 …)
2:rank() over(partition by [分组列] order by [排序列]) 函数简介
分组列和排序列同上
rank的群组内的排名方法为 如果出现两个相同的排序列时,那么下一个排序值为会自动加一
(1,1,3…)
3:dense_rank() over(partition by [分组列] order by [排序列]) 函数简介
分组列和排序列同上
dense_rank的群组内的排名方法为 如果出现两个相同的排序列时,那么下一个排序值不会出现跳跃
例(1,1,2,3 ..)
——————————————————
例:
create table A ([姓名] nvarchar(20),[订单数] int,[订单日期] datetime ) go insert into A ([姓名],[订单数],[订单日期]) values ('www.maomao365.com',1900,'2014-5-6') insert into A ([姓名],[订单数],[订单日期]) values ('www.maomao365.com',1800,'2018-5-6') insert into A ([姓名],[订单数],[订单日期]) values ('www.maomao365.com',1800,'2018-5-6') insert into A ([姓名],[订单数],[订单日期]) values ('小张',100,'2013-5-6') insert into A ([姓名],[订单数],[订单日期]) values ('小明',2600,'2013-1-6') insert into A ([姓名],[订单数],[订单日期]) values ('小明',1800,'2013-5-6') insert into A ([姓名],[订单数],[订单日期]) values ('小李',888,'2017-3-6') go /*row_number 返回分组后的连续排序,不会出现重复的排序值*/ select row_number() over(partition by [姓名] order by [订单日期] desc ) as keyId,* from A /*rank 返回分组后的连续排序,会出现跳跃排序值*/ select rank() over(partition by [姓名] order by [订单日期] desc ) as keyId,* from A /*dense_rank 返回分组后的连续排序,不会出现跳跃排序值,但是会出现重复的排序值*/ select dense_rank() over(partition by [姓名] order by [订单日期] desc ) as keyId,* from A go truncate table A drop table A