自定义函数实现字符串中数据的运算和统计2

上一篇是汇总操作，这一篇是求平均值,并且数据可能出现非数字和负数

问题:
数据表如下

cate            values                                  dates
 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-04-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01
 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-04-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01
 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-05-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01
 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-05-01
 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01
 A1           -12.32,15.6,14.5,45.00,78.12,23.45         2006-04-01
 A2           12.32,-15.6,14.5,无,78.12,23.45         　 2006-04-01
不用存储过程实现如下数据统计
 cate            T1                                     dates
A1   4.10333,15.6,14.5,44.6667,78.12,23.45              2006-04-01
A1   12.315,15.6,14.5,44.5,78.12,23.45                  2006-05-01
A2   13.8133,39.62,34.9133,15.4,49.1533,45.8033         2006-04-01
A2   14.56,67.23,45.12,23.1,34.67,56.98                 2006-05-01
==========================================================================
就是 values 值之间用,隔开的,当cate和dates 相同时,算values中各个以,隔开的数值的平均值,要求是不用存储过程实现

实现：

--建立环境

create table up(
cate  varchar(3),
[values]  varchar(50),
dates datetime
)

insert up select
 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-04-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'
union all select
 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-04-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'
union all select
 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-05-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'
union all select
 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-05-01'
union all select
 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'
union all select
 'A1','-12.32,15.6,14.5,45.00,78.12,23.45','2006-04-01'
union all select
 'A2','12.32,-15.6,14.5,无,78.12,23.45','2006-04-01'

go

--求平均值函数
create function fn_ValuesAvg(
@cate  varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
   declare @r varchar(50)
   set @r=''
   declare @t table (
      [values] varchar(50)
    )
   insert @t select [values] from up where cate=@cate and dates=@dates

   declare @t1 table (
      No int,
      [values] float
    )
   declare @No int

   set @No=1
   while exists (select 1 from @t where charindex(',',[values])>0)
   begin
        insert @t1 select @No,
             case when isnumeric(left([values],charindex(',',[values])-1))=1 then cast(left([values],charindex(',',[values])-1) as float) else 0 end
from @t

        update @t set [values]=stuff([values],1,charindex(',',[values]),'')
        set @No=@No+1
   end
   insert @t1 select @No,case when isnumeric([values])=1 then cast([values] as float) else 0 end from @t

   select @r=@r+','+cast(avg([Values]) as varchar) from @t1 group by No order by No

   return stuff(@r,1,1,'')
end
go

--查询
select cate,dbo.fn_valuesavg(cate,dates) as T,dates
from up
group by cate,dates

--结果

cate T                                                  dates                                                 
---- -------------------------------------------------- ------------------------------------------------------
A1   4.10333,15.6,14.5,44.6667,78.12,23.45              2006-04-01 00:00:00.000
A1   12.315,15.6,14.5,44.5,78.12,23.45                  2006-05-01 00:00:00.000
A2   13.8133,39.62,34.9133,15.4,49.1533,45.8033         2006-04-01 00:00:00.000
A2   14.56,67.23,45.12,23.1,34.67,56.98                 2006-05-01 00:00:00.000

（所影响的行数为 4 行）