up有分,大家看看如何实现
有一表结构如下
cate values dates
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
A3 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A3 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
数据的录入都是在每个季度下一月,但dates 显示的是每个季度最后一个月的的第一天.比如:
================================================================
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
================================================================
这条条记录是在四月分录入的而dates显示的是第一季度最后一个月的的第一天.该表的数据在导入时就生成好了,现在我想建立一视图:现在是8月也就是第三季度 ,我要列出所有第二季度(当前季度的上一个季度)的数据如下:
cate values dates
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
A3 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
但这些数据的values要做一些变动(values是varchar类型的,字符之间用,隔开)拿A1来说,也就是第3个逗号和第4个逗号之间的数据(44.00)加上所有小于这个季度(第二季度)并且cate相同记录的同样是第3个逗号和第4个逗号之间的数据(45.00)的和除以2,得到z最终的试图格式如下:
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A1 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
A3 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A3 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
第一季度的数据没变化,第二季度的数据的第三个逗号和第四个逗号的数据发生了变化,请问该如何实现以上数据,如果变化的不止一个地方比如:第二逗号和第三个逗号,第4个逗号和第5个逗号之间的数据都呀发生同样变化.不用存储过程如何实现?可以用函数!
[解决办法]
记得上次的函数是我写的
[解决办法]
还是没说明白需求
如果有三个季度数据，那显示的数据是不是第一季度的不变，第二季度的第4个逗号和第5个逗号之间的数据变成1 2季度的平均，其他不变，第二季度的第4个逗号和第5个逗号之间的数据变成1 2 3季度的平均，其他不变？
还有，一次查询是不是变的数据的位置都是相同的
[解决办法]
這麼多看了都頭暈。。。
[解决办法]
还有，一个季度一个cate是不是保证只有一条记录？
[解决办法]
up
[解决办法]
函数完成
--求部分平均值函数
create function fn_ValuesAvg1(
@cate varchar(3),
@dates datetime,
@Data int --这个参数表示哪些数据需要改变，用未来表示，比如1表示改变第一个数据，3便是改变第一和第二，5表示改变第一和第三
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
declare @t table (
dates datetime,
[values] varchar(50)
)
insert @t select dates,[values] from up where cate=@cate and year(dates)=year(@dates) and dates <=@dates --同一年的之前数据来求平均
declare @t1 table (
No int,
dates datetime,
[values] float
)
declare @No int
set @No=0 --改成从0开始，好计算
while exists (select 1 from @t where charindex( ', ',[values])> 0)
begin
insert @t1 select @No,dates,
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,dates,case when isnumeric([values])=1 then cast([values] as float) else 0 end from @t
select @r=@r+ ', '+case when (power(2,a.No) & @Data) <> 0 then cast(b.[avgV] as varchar) else cast(a.[Values] as varchar) end
from @t1 a,(
select No,avg([Values]) as avgV from @t1 group by No
) as b
where a.no=b.no and a.dates=@dates
return stuff(@r,1,1, ' ')
end
go
--查询
select cate,dbo.fn_valuesavg1(cate,dates,8) as T,dates
from up
group by cate,dates
--结果
cate T dates
---- -------------------------------------------------- ------------------------------------------------------
A1 12.32,15.6,14.5,45,78.12,23.45 2006-03-01 00:00:00.000
A1 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01 00:00:00.000
A3 12.32,15.6,14.5,45,78.12,23.45 2006-03-01 00:00:00.000
A3 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01 00:00:00.000
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01 00:00:00.000
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01 00:00:00.000
（所影响的行数为 8 行）
[解决办法]
需要说明下
@Data int 参数的用法
如果你的values有6个数据那
@Data有效去值范围是 0 到 2^6-1=63
分别意义如下：
@data 二进制 意义
0 000000 都不求平均
1 000001 第1个数据求平均
2 000010 第2个数据求平均
3 000011 第1、2个数据求平均
4 000100 第3个数据求平均
5 000101 第1、3个数据求平均
6 000110 第2、3个数据求平均
7 000111 第1、2、3个数据求平均
8 001000 第4个数据求平均
9 001001 第1、4个数据求平均
10 001010 第2、4个数据求平均
11 001011 第1、2、4个数据求平均
...
63 111111 第1、2、3、4、5、6个数据求平均
你需要根据数据多少以及你需要改变的数据的位置决定你这个参数的选择
[解决办法]
up 一下先
[解决办法]
up
[解决办法]
有这么多，那参数方式必须改变了，bigint也不够
[解决办法]
--求部分平均值函数
alter function fn_ValuesAvg1(
@cate varchar(3),
@dates datetime,
@Data varchar(50)
)
returns varchar(50)
as
begin
declare @r varchar(50)
set @r= ' '
declare @t table (
dates datetime,
[values] varchar(50)
)
insert @t select dates,[values] from up where cate=@cate and year(dates)=year(@dates) and dates <=@dates --同一年的之前数据来求平均
declare @t1 table (
No int,
dates datetime,
[values] float
)
declare @No int
set @No=1 --改回从1开始，好计算
while exists (select 1 from @t where charindex( ', ',[values])> 0)
begin
insert @t1 select @No,dates,
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,dates,case when isnumeric([values])=1 then cast([values] as float) else 0 end from @t
select @r=@r+ ', '+case when charindex( ', '+cast(a.no as varchar)+ ', ', ', '+@Data+ ', ')> 0 then cast(b.[avgV] as varchar) else cast(a.[Values] as varchar) end
from @t1 a,(
select No,avg([Values]) as avgV from @t1 group by No
) as b
where a.no=b.no and a.dates=@dates
return stuff(@r,1,1, ' ')
end
go
--查询
select cate,dbo.fn_valuesavg1(cate,dates, '2,4,6 ') as T,dates
from up
group by cate,dates
--结果
cate T dates
---- -------------------------------------------------- ------------------------------------------------------
A1 12.32,15.6,14.5,45,78.12,23.45 2006-03-01 00:00:00.000
A1 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01 00:00:00.000
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01 00:00:00.000
A3 12.32,15.6,14.5,45,78.12,23.45 2006-03-01 00:00:00.000
A3 12.31,15.6,14.5,44.5,78.12,23.45 2006-06-01 00:00:00.000
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01 00:00:00.000
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01 00:00:00.000
（所影响的行数为 8 行）
[解决办法]
参数直接拼成字符串
[解决办法]
up
[解决办法]
Haiwer(海阔天空) ( ) 信誉：138
-----------------
写这方面的函数还真是很厉害啊
学习学习~~~~
[解决办法]
厉害！
[解决办法]
学习学习
[解决办法]
学习一下
[解决办法]
头晕
[解决办法]
只能学习了
[解决办法]
学习！！！
[解决办法]
up！
[解决办法]
up
