求用sql或存储过程实现excel函数 percentile

补充：例如 一个表有两个字段(ID, Salary),表有10行数据如下：
1    50
2    150
3    100
4    200
5    200
6    300
7    350
8    400
9    400
10  500

用 excel PERCENTILE 函数运算结果

语法 
PERCENTILE(array,k)  
array 就是 salary字段数据，当k=0.5 结果是250，但k=0.75 结果是387.5

如何用sql 或储存过程实现这个PERCENTILE函数呢？

请各位达人帮帮忙！

答案：create   table   tb(Class   int,Salary   int,Median   decimal(10,2))    
   insert   tb   select   1,50,NULL    
   union   all   select   2,150,NULL    
   union   all   select   3,100,NULL    
   union   all   select   4,200,NULL    
   union   all   select   5,200,NULL    
   union   all   select   6,300,NULL    
   union   all   select   7,350,NULL    
   union   all   select   8,400,NULL    
   union   all   select   9,400,NULL    
   union   all   select   10,500,NULL    
   go    
      

   create   function   f_calc(    
   @K   decimal(3,2),    
   @Class   int    
   )returns   decimal(10,2)    
   as    
   begin    
   declare   @re   decimal(10,2)    
   if   @K   between   0   and   1      
   begin    
   declare   @array   table(id   int   identity(1,1),value   int)    
   insert   @array   select   Salary      
   from   tb    
  order   by   Salary    
   set   @re=(1-((@@rowcount-1)*@K-ROUND((@@rowcount-1)*@K,0,-1)))    
   *(select   value   from   @array   where   id=ROUND((@@rowcount-1)*@K,0,-1)+1)    
   +((@@rowcount-1)*@K-ROUND((@@rowcount-1)*@K,0,-1))    
   *(SELECT   value   from   @array   where   id=ROUND((@@rowcount-1)*@K,0,-1)+2)    
   end    
   return(@re)    
   end    
   go    
      
  select dbo.f_calc(0.5,0)    ,dbo.f_calc(0.75,0) from tb
go
drop table tb
drop  function   f_calc

上一个：excel表格的问题？
下一个：excel AB两个表都包含字段1字段2 字段1 字段2都不是唯一,但是字段1+字段2为唯一