表A(ID,hsource,name)数据类型(int,varchar(100),varchar(100))
ID hsource name
1 34,123,98,87 a
2 56,98,2,1 a
... ... ....
求各name的hsource平均值。
ID hsource name
1 34,123,98,87 a
2 56,98,2,1 a
... ... ....
求各name的hsource平均值。
create function f_tb(@str varchar(8000))
returns @tb table([平均值] varchar(20))
as
begin
while charindex(',',@str)>0
begin
insert @tb
select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
insert @tb select @str
return
end
go
declare @str varchar(8000)
set @str=select hsource from 表A
select name as 名称,avg(select avg(平均值) from f_tb(@str) ) as 平均值 from 表A group by name
returns @t table(col varchar(20))
as
begin while(charindex(@split,@c) <> 0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c), ' ')
end
insert @t(col) values (@c)
return
end
go
select (select avg(convert(float,col)) from dbo.f_split(hsource, ',')) hsource,name from A