前80%的产品,结果输出排名产品名称销售金额占比累计占比

查询所有时间内,所有产品销售金额占比,按占比大小降序排序,筛选累计占比在前80%的产品,结果输出排名产品名称销售金额占比累计占比

;WITH cte1 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY SUM(SALE_AMOUNT*PRICE) DESC) as ID, PRODUCTNAME AS 产品名称, SUM(SALE_AMOUNT*PRICE) as 销售金额
    FROM   订单
           INNER JOIN 产品
                ON  订单.PRODUCTID = 产品.PRODUCTID
    GROUP BY
           PRODUCTNAME
    ),cte2 as(
    select sum(销售金额) as 总销售金额
    from cte1
    ),cte3 as (
    SELECT cte1.ID,cte1.产品名称,cte1.销售金额 ,convert(decimal(18,2),cte1.销售金额*100.0/cte2.总销售金额) AS 占比
    FROM cte1,cte2
    )    ,cte4 as (
    SELECT ID,占比,cast(占比 as float) as 累计占比
      FROM cte3
     WHERE ID = 1
    UNION ALL -- 递归
    SELECT cte3.ID,         
           cte3.占比,
           cte4.累计占比 + cte3.占比
      FROM cte4
      JOIN cte3 ON cte3.ID = cte4.ID + 1
     WHERE cte4.累计占比 <=75
)
SELECT b.ID as 排名, a.产品名称,a.销售金额,b.占比,b.累计占比
from cte4 as b
left join cte3 as a 
on a.ID=b.ID