利用函数逐行对表进行求最大数、最小数、N个最大数的平均值、N个最小数的平均值

最新推荐文章于 2021-07-27 10:18:20 发布

feegle_develop

最新推荐文章于 2021-07-27 10:18:20 发布

阅读量726

点赞数

分类专栏： MSSQL 文章标签： function fun table object insert null

本文链接：https://blog.csdn.net/feegle_develop/article/details/5600918

版权

MSSQL 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

--利用函数逐行对表进行求最大数、最小数、N个最大数的平均值、N个最小数的平均值
if OBJECT_ID('t') is not null
drop table t
go

create table t
(
col1 decimal(18,4),
col2 decimal(18,4),
col3 decimal(18,4),
col4 decimal(18,4),
col5 decimal(18,4)
)
go

insert into t
select 3.25,5.46,6.86,45.26,21.35
union all
select 23.26,15.37,26.46,58.19,25.64
union all
select 56.13,28.34,19.46,98.10,46.82
go

if OBJECT_ID('fun_min') is not null
drop function fun_min
go

--求表每行最小值
create function fun_min(@col1 decimal(18,4),@col2 decimal(18,4),@col3 decimal(18,4),@col4 decimal(18,4),@col5 decimal(18,4))
returns decimal(18,4)
as
begin
    declare @t table(col decimal(18,4))
    insert into @t
    select @col1
    union all
    select @col2
    union all
    select @col3
    union all
    select @col4
    union all
    select @col5

    return (select min(col) from @t)
    --每行最大值
    --return (select max(col) from @t)
end
go

select dbo.fun_min(col1,col2,col3,col4,col5) from t
go

if OBJECT_ID('fun_avg') is not null
drop function fun_avg
go

--求表每行前三个最大数平均值
create function fun_avg(@col1 decimal(18,4),@col2 decimal(18,4),@col3 decimal(18,4),@col4 decimal(18,4),@col5 decimal(18,4))
returns decimal(18,4)
as
begin
declare @t table(col decimal(18,4))
insert into @t
select @col1
union all
select @col2
union all
select @col3
union all
select @col4
union all
select @col5

return (select avg(col) from (select top 3 col from @t order by col desc) a)
--求表每行前三个最小数平均值
--return (select avg(col) from (select top 3 col from @t order by col asc) a)
end
go

select dbo.fun_avg(col1,col2,col3,col4,col5) from t
go