SQL 选择前几行总和大于某个值

if object_id('test') is not null
drop table tset
go
create table test(
empid char(4),
deptid char(3),
salary int
)
go
insert test
select '1001','101',2000 union all
select '1002','101',1500 union all
select '1003','103',1900 union all
select '1004','102',3500 union all
select '1005','103',2800 union all
select '1006','101',1500 union all
select '1007','104',1600 union all
select '1008','104',2100 union all
select '1009','102',5000 union all
select '1010','103',4500 union all
select '1011','104',3200 
 
;with t
as(
select 
id=row_number()over(order by salary desc),
empid,deptid,salary
from test
),
m as(
select 
id,empid,deptid,salary,salary as total
from t where id=1
union all
select 
t.id,t.empid,t.deptid,t.salary,m.total+t.salary
from t 
inner join m
on t.id=m.id+1
)
 
select 
id,empid,deptid,salary,total,
left(ltrim((cast(total as numeric(10,2))/
cast((select sum(salary) from test) as numeric(10,2)))*100),5)+'%' as precent
from m
 
/*
id  empid  deptid  salary  total  precent
------------------------------------------------
1    1009    102    5000    5000    16.89%
2    1010    103    4500    9500    32.09%
3    1004    102    3500    13000    43.91%
4    1011    104    3200    16200    54.72%
5    1005    103    2800    19000    64.18%
6    1008    104    2100    21100    71.28%
7    1001    101    2000    23100    78.04%
8    1003    103    1900    25000    84.45%
9    1007    104    1600    26600    89.86%
10    1002    101    1500    28100    94.93%
11    1006    101    1500    29600    100.0%
*/