业务饼图实现

收到这样一个业务需求，取某个时间段内销售最好的5个商品，多于5个商品则其余的商品都归入其它，如果少于等于5个商品则不需要有其它
如图所示

---构造测试用例
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 + 1
)
,cte4 as
(
  select rowno,a.goodsid,b.goodsname,qty,-1 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 cte2)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 =-1 or allcnt >= rowcnt
order by rowno

复制