在线求大神查询出这句SQL语句

Question

Answer 1

with t as (
	select 1 as 产品ID,'A' as 产品名称,100 as 销售额
	union all select 2,'B',300
	union all select 3,'C',200
	union all select 4,'D',400
	union all select 5,'E',50
	union all select 6,'F',500
	union all select 7,'G',600
),t1 as (
    -- 获取当天总销售额
	select SUM(销售额) as 总销售额 from t
),t2 as (
    -- 获得当天每个产品销售额占比，并倒序排序
	select *,convert(decimal(8,2),销售额)/总销售额*100 as 销售占比
		,ROW_NUMBER() over(order by convert(decimal(8,2),销售额)/总销售额*100 desc) as rid
	from t,t1
)
select 产品ID,产品名称,销售额 
from (
    -- 用销售额最高的项，累加销售占比，小于80的一组，大于等于80的一组
	select *,row_number() over(partition by (case when 累计占比<80 then 0 else 1 end) order by 累计占比) as nid
	from t2 a
	cross apply(
		select (
			select SUM(销售占比)
			from t2
			where rid<=a.rid
		) as 累计占比
	) b
) a
-- 筛选出累计占比小于80的，和大于等于80的第一项
where 累计占比<80 or nid=1