问题:
数据表如下
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
不用存储过程实现如下数据统计
cate T1 dates
A1 24.63,31.2,29.0,89.0,156.24,46.9 2006-04-01
A1 24.63,31.2,29.0,89.0,156.24,46.9 2006-05-01
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-04-01
A2 29.12,134.46,90.24,46.2,69.34,113.96 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'
go
--加法函数
create function fn_Plus(
@s1 varchar(50),
@s2 varchar(50)
)
returns varchar(50)
as
begin
if @s1 is null or @s1=''
return @s2
if @s2 is null or @s2=''
return @s1
declare @r varchar(50)
set @r=''
declare @f1 float
declare @f2 float
while charindex(',',@s1)>0 and charindex(',',@s2)>0
begin
set @f1=left(@s1,charindex(',',@s1)-1)
set @f2=left(@s2,charindex(',',@s2)-1)
set @s1=stuff(@s1,1,charindex(',',@s1),'')
set @s2=stuff(@s2,1,charindex(',',@s2),'')
set @r=@r+','+cast(@f1+@f2 as varchar)
end
if @s1<>'' and @s2<>''
set @f1=@s1
set @f2=@s2
set @r=@r+','+cast(@f1+@f2 as varchar)
if @r<>''
set @r=stuff(@r,1,1,'')
return @r
end
go
--求值函数
create function fn_Values(
@cate varchar(3),
@dates datetime
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r=''
select @r=dbo.fn_Plus(@r,[values]) from up where cate=@cate and dates=@dates
return @r
end
go
--查询
select cate,dbo.fn_values(cate,dates) as T,dates
from up
group by cate,dates
--结果
cate T dates
---- -------------------------------------------------- ------------------------------------------------------
A1 24.63,31.2,29,89,156.24,46.9 2006-04-01 00:00:00.000
A1 24.63,31.2,29,89,156.24,46.9 2006-05-01 00:00:00.000
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-04-01 00:00:00.000
A2 29.12,134.46,90.24,46.2,69.34,113.96 2006-05-01 00:00:00.000
（所影响的行数为 4 行）
发表于 @ 2007年07月23日 11:06:00|评论(loading...)|编辑