sql server 获取某一字段分组数据的前十条记录

1、sql 语法

select m, n
from (select row_number () over (partition by m order by n desc) rn,--以m分组，分组内以n倒序排列求每组中各自的序号m, nfrom tablewhere ...
) w
where w.rn <=10;序号小于10
order by m, n desc

2、案例获取每个月前十大客户数据

原来数据

案例sql

select
StatDate,
OrderCount,
AmountTotal,
CustomerUnitPrice
from(selectrow_number () over (partition by t.StatDate order by t.AmountTotal desc) rn,*from ( select CONVERT(varchar(7), AuditTime, 120) StatDate,FBM_USER_ID CustomerId,Count(1) OrderCount,sum(isnull(FBM_BLL_BOOK_TOTAL,0)) AmountTotal,case when sum(isnull(FBM_BLL_BOOK_TOTAL,0)) >0 then Round((sum(isnull(FBM_BLL_BOOK_TOTAL,0)) / Count(1) + 0.0), 4) else  0 end as CustomerUnitPricefrom F_CUST_BOOK_MSTR bwhere auditStatus=2 and AuditTime is not nulland AuditTime>='2017-06-01'group by CONVERT(varchar(7), AuditTime, 120),FBM_USER_ID) t
) tt
where rn <=10
order by StatDate desc

查询结果