mssql sqlserver 数据表分组排序和组内排序方法分享


摘要:
下文主要讲述,数据表中数据,
根据一个或多个字段群组后,然后将每一行的数据在群组中的按指定列的顺序进行组内编号,
方便后续的去重,取最新一条相关数据做准备。
————————————————————–
下文实验环境:
sql server 2008 R2 版本


例:

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 ('小张',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

/*例1:根据客户名称,进行群组,并根据组内订单数生成组内排序列keyId*/
select * from (
select row_number() over (Partition By [姓名] order by [订单数] desc)as keyId,* from A 
) as [A2]  
/*例2:根据客户名称,进行群组,并根据组内订单日期生成组内排序列keyId*/
select * from (
select row_number() over (Partition By [姓名] order by [订单日期] desc)as keyId,* from A 
) as [A2] 
/*例3---多字段进行群组:根据客户名称和订单数,进行群组,并根据组内订单日期生成组内排序列keyId*/
select * from (
select row_number() over (Partition By [姓名] order by [订单日期] desc)as keyId,* from A 
) as [A2] 

go
truncate table A
drop     table A 

mssql_sqlserver分组排序_组内排序列

mssql_sqlserver分组排序_组内排序列

q