declare @tb table(serv_id int,A_ALL int)
insert into @tb
select 3256,20
union all select 4585,32
union all select 2586,40
union all select 5226,40
union all select 5226,400
union all select 4208,50
union all select 4208,500 select serv_id,sum(A_ALL)A_ALL from @tb group by serv_id having sum(A_ALL)>=(SELECT sum(A_ALL)*0.3 FROM @TB)
ORDER BY sum(A_ALL) DESC
insert into @tb
select 3256,20
union all select 4585,32
union all select 2586,40
union all select 5226,40
union all select 5226,400
union all select 4208,50
union all select 4208,500 select serv_id,sum(A_ALL)A_ALL from @tb group by serv_id having sum(A_ALL)>=(SELECT sum(A_ALL)*0.3 FROM @TB)
ORDER BY sum(A_ALL) DESC
serv_id int,
a_all int
)
goset nocount on
goinsert into salary
select 3256, 20 union all
select 4585, 32 union all
select 2586, 40 union all
select 5226, 40 union all
select 4208, 50
goselect * into # from salary order by a_all desc
godeclare @i int
select @i=sum(a_all) from #
set @i = @i*0.3
select top 1 * from # a
where (select sum(b.a_all) from # b where b.a_all>=a.a_all) >= @i order by a.a_all desc
go=========================
serv_id a_all
----------- -----------
5226 40
select 4568,33 union all
select 5863,58 union all
select 1589,25 union all
select 6868,60 union all
select 5869,45
go得到的结果serv_id a_all
----------- -----------
6868 60
5863 58
4208 50
5869 45
2586 40
5226 40
4568 33
4585 32
1589 25
3256 20
(select serv_id from (select serv_id,(select sum(A_All) from #temp where id<=a.id) a_all,
(select sum(a_all) from #temp)cnt from #temp a)v
where a_all=0.3*cnt)