如何计算每行最大三个值的平均值

Question

Value+1 Value+2 Value+3 Value+4 Value+5 Value+6 Value+7
4.39 12.13 13.97 3.04 4.06 9.54 5.05如上表，有几万行数据，我想算出每行最大三个值的平均值，如何实现，非常感谢

Answer 1

SQL SERVER多列取最大或者最小值
/*lvl1  lvl2    lvl3    lvl4    lvl 
4      3      4      1        
3      2      2      1    
2      2      3      4 
4      4      3      4 
3      1      2      2 
怎么写代码 去比较lvl1、lvl2、lvl3、lvl4 对应每行的值，取其中最小的，将其值添加到lvl列里 
运行结果应该是 
lvl 
1 
1 
2 
3 
1*/--方法(一) 函數法-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@col1 int,@col2 int,@col3 int,@col4 int)
returns int
as
  begin
     declare @t table(col int)
     insert @t select @col1 union all
               select @col2 union all
               select @col3 union all
               select @col4
     return(select min(col)from @t)
  end
go
update t set [lvl]=dbo.UF_minget([lvl1],[lvl2],[lvl3],[lvl4])
from #t t
select * from #t
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1(5 個資料列受到影響)
*/--方法二  MSSQL2005 XML PATH-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-10-16 09:57:38
---------------------------------------> 生成测试数据: @T
DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)
INSERT INTO @T
SELECT 4,3,4,1,null UNION ALL
SELECT 3,2,2,1,null UNION ALL
SELECT 2,2,3,4,null UNION ALL
SELECT 4,4,3,4,null UNION ALL
SELECT 3,1,2,2,null--SQL查询如下:UPDATE A SET
    lvl = B.x.value('min(//row/*)','int')
FROM @T AS A
    CROSS APPLY (SELECT x = (SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
    
SELECT * FROM @T;/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1(5 行受影响)*/
--方法(三) 作者 (四方城) if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
insert [tb]
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
gocreate function getmin(@a varchar(8000))   
  returns int   
  as   
  begin declare @ table (id int identity,a char(1))   
      declare @t int   
      insert @ select top 8000 null from sysobjects a,sysobjects b   
      select @t=min(cast(substring(','+@a,id+1,charindex(',',','+@a+',',id+1)-id-1) as int))   
      from @ where substring(','+@a,id,8000) like ',_%'   
      return @t   
  end   
go-->查询
select 
  lvl1,
  lvl2,
  lvl3,
  lvl4,
  lvl=dbo.getmin(ltrim(lvl1)+','+ltrim(lvl2)+','+ltrim(lvl3)+','+ltrim(lvl4))
from tb/**
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1(5 行受影响)
**/--方法(四)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Goif object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
  begin
  return(
    select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
    from master..spt_values
    where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
  end
go
select 
  [lvl1],
  [lvl2],
  [lvl3],
  [lvl4],
  [lvl]=dbo.UF_minget(ltrim([lvl1])+','+ltrim([lvl2])+','+ltrim([lvl3])+','+ltrim([lvl4]))
from #T
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1*/--方法(五)-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
    drop table #t
Go
Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)
Insert #t
select 4,3,4,1,null union all
select 3,2,2,1,null union all
select 2,2,3,4,null union all
select 4,4,3,4,null union all
select 3,1,2,2,null
Go
select [lvl1],
       [lvl2],
       [lvl3],
       [lvl4],
       [lvl]=(select min([lvl1])
              from (select [lvl1] 
                  union all select [lvl2] 
                  union all select [lvl3] 
                  union all select [lvl4])T)
from #t
/*
lvl1        lvl2        lvl3        lvl4        lvl
----------- ----------- ----------- ----------- -----------
4           3           4           1           1
3           2           2           1           1
2           2           3           4           2
4           4           3           4           3
3           1           2           2           1(5 個資料列受到影響)
*/

Answer 2

有其他字段吗？
加时为ID
select * into #
from(
select ID,[Value+1][Value] from tb
union all
select ID,[Value+2] from tb
union all
select ID,[Value+3] from tb
union all
select ID,[Value+4] from tb
union all
select ID,[Value+5] from tb
union all
select ID,[Value+6] from tb
union all
select ID,[Value+7] from tb
)t
select ID,sum([Value])[Value]
from(
select *
from # t
where (select count(*)
       from # 
       where ID=t.id and [Value]>t.[Value])<3
)t
group by ID
go
drop table #

Answer 3

DECLARE @TB TABLE([Value+1] FLOAT, [Value+2] FLOAT, [Value+3] FLOAT, [Value+4] FLOAT, [Value+5] FLOAT, [Value+6] FLOAT, [Value+7] FLOAT)
INSERT @TB 
SELECT 4.39, 12.13, 13.97, 3.04, 4.06, 9.54, 5.05SELECT *,(SELECT SUM(VAL)/3 
FROM (
SELECT TOP 3 VAL FROM (
SELECT [Value+1] AS VAL UNION ALL
SELECT [Value+2] UNION ALL
SELECT [Value+3] UNION ALL
SELECT [Value+4] UNION ALL
SELECT [Value+5] UNION ALL
SELECT [Value+6] UNION ALL
SELECT [Value+7]) T
        ORDER BY VAL DESC
)T2) AS AVGVAL
FROM @TB 
/*
Value+1                Value+2                Value+3                Value+4                Value+5                Value+6                Value+7                AVGVAL
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
4.39                   12.13                  13.97                  3.04                   4.06                   9.54                   5.05                   11.88
*/

Answer 4

DECLARE @TB TABLE([Value+1] FLOAT, [Value+2] FLOAT, [Value+3] FLOAT, [Value+4] FLOAT, [Value+5] FLOAT, [Value+6] FLOAT, [Value+7] FLOAT)
INSERT @TB 
SELECT 4.39, 12.13, 13.97, 3.04, 4.06, 9.54, 5.05 union 
SELECT 100.39, 188.13, 181.97, 3.04, 4.06, 9.54, 5.05 
select IDENTITY(int,1,1) as id,* into poo from @TB
declare @s varchar(8000)
select  @s=ISNULL(@s+',','')++'['+name+']' from syscolumns where id=OBJECT_ID('poo')
set @s=stuff(REPLACE(@s,',',' from poo  union all  select id, '),1,len('  id from poo union  all'),'')+' from poo '
exec('select * into # from ('+@s+') z ;
     select avg([Value+1]) as avgvalue
     from(
     select * from # k where [Value+1] in (select top 3 [Value+1] from # where k.id=id order by [Value+1] desc)
     ) zo 
     group by id
')
/*
avgvalue
----------------------
11.88
156.83
*/

Answer 5

create  TABLE tab([Value+1] FLOAT, [Value+2] FLOAT, [Value+3] FLOAT, [Value+4] FLOAT, [Value+5] FLOAT, [Value+6] FLOAT, [Value+7] FLOAT)
INSERT tab 
SELECT 4.39, 12.13, 13.97, 3.04, 4.06, 9.54, 5.05select avg(a) 平均值
from (
select top 3 a
from tab unpivot (a for b in([Value+1],[Value+2],[Value+3],[Value+4],[Value+5],[Value+6],[Value+7]))a
order by a desc
)a
/*
平均值
11.88
*/

Answer 6

if OBJECT_ID('t') is not null
  drop table t
gocreate table t
(
col1 decimal(18,4),
col2 decimal(18,4),
col3 decimal(18,4),
col4 decimal(18,4),
col5 decimal(18,4)
)
goinsert 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
goif OBJECT_ID('fro_max') is not null
  drop function fro_max
goalter function fro_max(@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 @col5return (select avg(col) from (select top 3 col from @t order by col desc) a)
end
goselect dbo.fro_max(col1,col2,col3,col4,col5) from t
go