27,580
社区成员
发帖
与我相关
我的任务
分享
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 表
where Class=@Class 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
--测试
--测试数据
create table 表(Class int,Salary int,Median decimal(10,2))
insert 表 select 1,1000,NULL
union all select 1,2000,NULL
union all select 1,3000,NULL
union all select 1,4000,NULL
union all select 2,5000,NULL
union all select 2,6000,NULL
union all select 2,7000,NULL
union all select 2,8000,NULL
union all select 2,9000,NULL
union all select 2,10000,NULL
go
--调用函数进行更新
update 表 set Median=dbo.f_calc(0.3,Class)
go
--显示更新结果
select * from 表
go
--删除测试
drop table 表
drop function f_calc
/*--测试结果
Class Salary Median
----------- ----------- ------------
1 1000 1900.00
1 2000 1900.00
1 3000 1900.00
1 4000 1900.00
2 5000 6500.00
2 6000 6500.00
2 7000 6500.00
2 8000 6500.00
2 9000 6500.00
2 10000 6500.00
(所影响的行数为 10 行)
--*/
看看
--借用楼上的计算公式来写计算函数
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 表
where Class=@Class 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
--测试
--测试数据
create table 表(Class int,Salary int,Median decimal(10,2))
insert 表 select 1,1000,NULL
union all select 1,2000,NULL
union all select 1,3000,NULL
union all select 1,4000,NULL
union all select 2,5000,NULL
union all select 2,6000,NULL
union all select 2,7000,NULL
union all select 2,8000,NULL
union all select 2,9000,NULL
union all select 2,10000,NULL
go
--调用函数进行更新
update 表 set Median=dbo.f_calc(0.3,Class)
go
--显示更新结果
select * from 表
go
--删除测试
drop table 表
drop function f_calc
/*--测试结果
Class Salary Median
----------- ----------- ------------
1 1000 1900.00
1 2000 1900.00
1 3000 1900.00
1 4000 1900.00
2 5000 6500.00
2 6000 6500.00
2 7000 6500.00
2 8000 6500.00
2 9000 6500.00
2 10000 6500.00
(所影响的行数为 10 行)
--*/
不过楼主的好像少一个参数