各位高手指教1、筛选出select AVG(data) as avg from testa where a='参数'的数据2、然后select data from testa where a='参数'的数据。循环的用每个符合条件的data减去avg。3、2步骤的循环完成后的总数 除以 "select count(data) from testa where a='参数'"个数4、最后输出3步骤得到的一个数字。感谢各位。
调试欢乐多
平均数据的总得就等于DATA的总和的循环的用每个符合条件的data减去avg。你这样减不等于0?????
如果理解没错的话:
sum(data)-count(data)*AVG(data) =0最后0/count(data)=0
insert into @t select 1,100,'s'
insert into @t select 2,200,'s'
insert into @t select 3,300,'s'
insert into @t select 4,400,'s'select (data-avg(data)over(partition by a))/count(1)over(partition by a)
from @t
where a='s'
/*
-37.5
-12.5
12.5
37.5
*/
http://beta.hi.csdn.net/link.php?url=http://blog.csdn.net%2Fjinjazz
http://blog.csdn.net/jinjazz/archive/2009/09/03/4516845.aspx
AS
BEGIN
SELECT * FROM TB WHERE ID=@ID
END
我的意思是你写的存储过程我看不太懂,是不是SQL语言的?能不能给我一些比较基础的常用的写法呢?
--- 用存储过程遍历表所有字段
--- 功能:删除ID字段外,其他所有字段都为空,或null 的记录
---自己研究吧use szy ---选择数据库 if object_id('ta')is not null drop table ta
go
create table ta(id int ,a varchar(50),b varchar(50),c varchar(50))
insert ta select
101, '1' , '2' , '3' union all select
102 , '4' , '5' , '6' union all select
103 , null ,null , null union all select
104 ,'' , null, ' ' union all select
105, ' ' , '2','3' if object_id('p_foreachField')is not null drop proc p_foreachField
go create proc p_foreachField
@tableName varchar(20),
@id intas
begin
--delete from tb ---删除表记录
create table #t(id int)
declare @colName varchar(20) ---列名
declare @colCount int ---列数
declare @n int
declare @F_or_T CHAR(1) ---判断真假
declare @str varchar(400) set @colName=''
set @n=1
set @F_or_T = 'F'
set @colCount=(select count(*) from syscolumns where object_name(id)= @tableName) while @n<=@colCount and @F_or_T = 'F'
if (select name from syscolumns where object_name(id)=@tableName and colid=@n)='id' --是id列不管
set @n=@n+1
else
begin
set @colName=(select name from syscolumns where object_name(id)=@tableName and colid=@n)
set @str='insert #t select 1 from '+@tableName +' where ('+@colName +' is not null and '+@colName +'<>'''') and id='+ltrim(@id)
exec(@str)
if (select count(*)from #t)>0
set @F_or_T = 'T'
set @n=@n+1
end
if @F_or_T='F'
begin
SET @STR='DELETE FROM '+@tableName+' where id='+ltrim(@id)
exec(@str)
end
endgo--------------exec p_foreachField 'ta',104select * from ta
id a b c
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
101 1 2 3
102 4 5 6
103 NULL NULL NULL
105 2 3(4 行受影响)
begin
declare @avg float
declare @cnt int
select @avg=avg(data),@cnt=count(*) from t where a='ss'
select (data-@avg)/@cnt from t where a='ss'
end