近期收到这样一个业务需求，取某个时间段内销售最好的5个商品，多于5个商品则其余的商品都归入其它，如果少于等于5个商品则不需要有其它
如图所示
goodsrate.png
---构造测试用例
declare @t table(goodsid int,qty numeric(24,10),saledate varchar(10))
declare @g table(goodsid int,goodsname varchar(30))
insert into @t
select 1,10,'2021-09-01' union all
select 2,20,'2021-09-01' union all
select 3,30,'2021-09-01' union all
select 1,10,'2021-09-02' union all
select 2,20,'2021-09-02' union all
select 3,30,'2021-09-03' union all
select 1,10,'2021-09-03'
insert into @g
select 1,'商品1'union all
select 2,'商品2'union all
select 3,'商品3'
--- 数据构造完毕
declare @topn int --取TOP N
set @topn = 2
;with cte1 as
(
select
goodsid,sum(qty) as qty from @t
group by goodsid
), cte2 as
(
select ROW_NUMBER() over(order by qty desc) as rowno ,goodsid,qty from cte1
)
,cte3 as
(
select rowno,a.goodsid,qty from cte2 a
where rowno <= @topn
)
,cte4 as
(
select rowno,a.goodsid,b.goodsname,qty,0 as allcnt from cte3 a
inner join @g b on a.goodsid = b.goodsid
union all
select @topn + 1 ,-1,'其它商品',t1.qty - t2.qty,t1.allcnt from
(select sum(qty) as qty,count(*) as allcnt from cte1)as t1 cross join
(select sum(qty) as qty from cte3) as t2
)
,cte5 as
(
select a.*,
case when t1.qty = 0 then '0' else cast(cast(round(a.qty/t1.qty*100,2) as real) as varchar) end + '%'as qtyrate ,
count(*) over() rowcnt
from cte4 a
cross join (select sum(qty) as qty from cte4) as t1
)
select *,@topn as topn from cte5
where allcnt =0 or allcnt >= rowcnt
order by rowno
result.png
