找出>=80%的前N项数据

 

--找出>=80%的前N项数据
select 1 as Value,0.00 as P  INTo #T   
INSERT INTo #T select 18,0.00  
INSERT INTo #T select 14,0.00  
INSERT INTo #T select 3,0.00  
INSERT INTo #T select 13,0.00  
INSERT INTo #T select 5,0.00  
INSERT INTo #T select 20,0.00  
INSERT INTo #T select 7,0.00  
INSERT INTo #T select 9,0.00  
INSERT INTo #T select 12,0.00  
INSERT INTo #T select 10,0.00  
INSERT INTo #T select 8,0.00  
INSERT INTo #T select 15,0.00  
INSERT INTo #T select 22,0.00  
INSERT INTo #T select 30,0.00  

--计算百分比
Update #T set P=CAST(Value as numeric(18, 2))/(select sum(Value) from #T)   

--原始数据
select * from #T  Order By Value desc  

--SQL找到累计求和不超出90%的前N条记录
select  a.Value,a.P,SUM(b.P) as '累计求和' INTO #Result from #T a left join #T b on a.Value<=b.Value   
group by a.Value,a.P  having SUM(b.P)<=0.9 Order By a.Value desc  

---得到最后的结果数据
select Value,p,累计求和 from #Result where 累计求和<=(select Min(累计求和) from #Result where 累计求和>=0.8)

drop table #Result  
drop table #T  

--------------------------------------------------------------方式二--------------------------------------------------------

--找出>=80%的前N项数据
select 1 as Value,0.00 as P  INTo #T   
INSERT INTo #T select 18,0.00  
INSERT INTo #T select 14,0.00  
INSERT INTo #T select 3,0.00  
INSERT INTo #T select 13,0.00  
INSERT INTo #T select 5,0.00  
INSERT INTo #T select 20,0.00  
INSERT INTo #T select 7,0.00  
INSERT INTo #T select 9,0.00  
INSERT INTo #T select 12,0.00  
INSERT INTo #T select 10,0.00  
INSERT INTo #T select 8,0.00  
INSERT INTo #T select 15,0.00  
INSERT INTo #T select 22,0.00  
INSERT INTo #T select 30,0.00  

--计算百分比
Update #T set P=CAST(Value as numeric(18, 2))/(select sum(Value) from #T)   

declare @p  decimal(18, 2)=0.80

--原始数据
select * from #T  order by Value desc
--排序数据
select value,p,0.0 as _P into #data from #T order by Value desc
--依次递减
update #data set @p=@p-p,p=@p,_P=@p

select * from #data where _P>=0 order by Value desc

drop table #data
drop table #T