sql 根据某一字段分组数据查询每组前十条记录
1、sql 语法select m, nfrom (select row_number () over (partition by m order by n desc) rn,--以m分组,分组内以n倒序排列求每组中各自的序号m, nfrom tablewhere ...) wwhere w.rn <=10;序号小于10order by m, n desc2、案例获取每个月前十大客户数据原来数
·
1、sql 语法
select m, n from ( select row_number () over (partition by m order by n desc) rn,--以m分组,分组内以n倒序排列求每组中各自的序号 m, n from table where ... ) w where w.rn <=10;序号小于10 order by m, n desc
2、案例获取每个月前十大客户数据
原来数据
案例sql
select StatDate, OrderCount, AmountTotal, CustomerUnitPrice from ( select row_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 CustomerUnitPrice from F_CUST_BOOK_MSTR b where auditStatus=2 and AuditTime is not null and AuditTime>='2017-06-01' group by CONVERT(varchar(7), AuditTime, 120),FBM_USER_ID ) t ) tt where rn <=10 order by StatDate desc
更多推荐
已为社区贡献2条内容
所有评论(0)