关于用sql语句求平均值,就是把一个表中的所有字段按时间字段的小时来求平均值,而不是按时间段求 ,例如有如下表
a b c time
12 13 45 2009-6-9 10:59:26
11 41 43 2009-6-9 10:59:30
10 75 742 2009-6-9 11:00:40
78 72 56 2009-6-9 11:30:50
52 11 55 2009-6-9 12:00:42
11 41 43 2009-6-9 12:59:30
我想的是根据time 中的小时来对a,b,c三个字段中该小时内的所有数据求平均值,当然不是一个小时数,而是以个时间段内的所有小时,例如上面的我选择2009-6-9 9:30:30 到2009-6-9 13:59:30 的时间段,那么得到的结果就是要对9点,10点 ,11点,12点,13点的各个字段所有数据求平均值,最后得到的结果是:
a b c time
平均值 平均值 平均值 2009-6-9 10:00:00
平均值 平均值 平均值 2009-6-9 11:00:00
平均值 平均值 平均值 2009-6-9 12:00:00
上面的平均值示例:例如在第一行的a的平均值应该是这样得到的,(11+12)/2=11.5即对10点中的所有a的数据求平均值。当然上面的结果中时间无所谓,例如10点只要是2009-6-9 10: 就行,后面的分秒已经没有意义。我想要的是用存储过程实现,当然SQL语句也行,我的数据库是MSSQL2000.
a b c time
12 13 45 2009-6-9 10:59:26
11 41 43 2009-6-9 10:59:30
10 75 742 2009-6-9 11:00:40
78 72 56 2009-6-9 11:30:50
52 11 55 2009-6-9 12:00:42
11 41 43 2009-6-9 12:59:30
我想的是根据time 中的小时来对a,b,c三个字段中该小时内的所有数据求平均值,当然不是一个小时数,而是以个时间段内的所有小时,例如上面的我选择2009-6-9 9:30:30 到2009-6-9 13:59:30 的时间段,那么得到的结果就是要对9点,10点 ,11点,12点,13点的各个字段所有数据求平均值,最后得到的结果是:
a b c time
平均值 平均值 平均值 2009-6-9 10:00:00
平均值 平均值 平均值 2009-6-9 11:00:00
平均值 平均值 平均值 2009-6-9 12:00:00
上面的平均值示例:例如在第一行的a的平均值应该是这样得到的,(11+12)/2=11.5即对10点中的所有a的数据求平均值。当然上面的结果中时间无所谓,例如10点只要是2009-6-9 10: 就行,后面的分秒已经没有意义。我想要的是用存储过程实现,当然SQL语句也行,我的数据库是MSSQL2000.
AVG(a) AS a,AVG(b) AS b,AVG(c) AS c
FROM tb
GROUP BY CONVERT(varchar(13),time,120)
这样吗?
能不能不一个一个写,像select *那样对所有的字段求平均值啊?我有个表200多个字段!!!!!!!!!!!要疯的!!!!
那就通过动态SQL来写,到syscolumns读字段名
能具体点吗?没用过动态SQL,
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int,[time] datetime)
insert [tb]
select 12,13,45,'2009-6-9 10:59:26' union all
select 11,41,43,'2009-6-9 10:59:30' union all
select 10,75,742,'2009-6-9 11:00:40' union all
select 78,72,56,'2009-6-9 11:30:50' union all
select 52,11,55,'2009-6-9 12:00:42' union all
select 11,41,43,'2009-6-9 12:59:30'declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'avg('+name+') as [avg'+name+']'
from sys.columns
where object_id=object_id('tb') and column_id<=3
set @sql='select '+@sql+',convert(varchar(13),time,120)+'+''':00:00'''+' AS [time] from tb group by convert(varchar(13),time,120)'
exec(@sql)
--测试结果:
/*
avga avgb avgc time
----------- ----------- ----------- -------------------
11 27 44 2009-06-09 10:00:00
44 73 399 2009-06-09 11:00:00
31 26 49 2009-06-09 12:00:00(3 行受影响)*/
go
create table [tb]([a] int,[b] int,[c] int,[time] datetime)
insert [tb]
select 12,13,45,'2009-6-9 10:59:26' union all
select 11,41,43,'2009-6-9 10:59:30' union all
select 10,75,742,'2009-6-9 11:00:40' union all
select 78,72,56,'2009-6-9 11:30:50' union all
select 52,11,55,'2009-6-9 12:00:42' union all
select 11,41,43,'2009-6-9 12:59:30'gocreate proc pr_test1
@s datetime,
@e datetime
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
if (datediff(d,@s,@e) < 30 )
set @g = ' convert(varchar(13),time,120)'
if ((datediff(d,@s,@e) >= 30 ) and (datediff(d,@s,@e) <= 365 ))
set @g = ' convert(varchar(10),time,120)'
if (datediff(d,@s,@e) > 365 )
set @g = ' convert(varchar(7),time,120)'
select @sql=isnull(@sql+',','')+'avg('+name+') as [avg'+name+']'
from syscolumns
where id=object_id('tb') and colid<=3
set @sql='select '+@sql+','+@g+' AS [time] from tb group by '+@g
exec(@sql)
end
goexec pr_test1 '2009-5-8','2009-7-10'
drop proc pr_test1
public void Bindsjcx()
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
SqlDataAdapter ada = new SqlDataAdapter();
SqlCommand sqlcom = new SqlCommand("sjcx", sqlcon);
SqlParameter pbegintime = new SqlParameter("@begintime", SqlDbType.DateTime);
pbegintime.Value = Convert.ToDateTime(sbegintime);
sqlcom.Parameters.Add(pbegintime); SqlParameter pendtime = new SqlParameter("@endtime", SqlDbType.DateTime);
pendtime.Value = Convert.ToDateTime(sendtime);
sqlcom.Parameters.Add(pendtime); SqlParameter psjb = new SqlParameter("@sjb", SqlDbType.VarChar,100);
psjb.Value = ssjb;
sqlcom.Parameters.Add(psjb); sqlcom.CommandType = CommandType.StoredProcedure;
ada.SelectCommand = sqlcom;
DataSet ds = new DataSet();
ada.Fill(ds,"ds");
if (ds.Tables["ds "] == null)
{
HttpContext.Current.Response.Write("出错拉 ");
HttpContext.Current.Response.End();
}
else
{ bsjcx.DataSource = ds; bsjcx.DataBind();
}
} private DateTime sbegintime ;
private DateTime sendtime;
private string ssjb; protected void cx_Click(object sender, EventArgs e)
{
sbegintime = Convert.ToDateTime(this.d4311.Value);
sendtime = Convert.ToDateTime(this.d4312.Value);
ssjb = Convert.ToString(this.sjb.Value);
Bindsjcx();
}
我加了 if (ds.Tables["ds "] == null)
{
HttpContext.Current.Response.Write("出错拉 ");
HttpContext.Current.Response.End();
}
else
{ bsjcx.DataSource = ds; bsjcx.DataBind();
}
提示出错了,说明没取到数据,sbegintime:2009-09-01 23:45:18;sendtime:2009-09-10 23:45:22;ssjb:guolu1;数据表guolu1里在这段时间内是有值的
@begintime datetime,
@endtime datetime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
if (datediff(d,@begintime,@endtime) < 30 )
set @g = 'convert(varchar(13),time,120)'
if ((datediff(d,@begintime,@endtime) >= 30 ) and (datediff(d,@begintime,@endtime) <= 365 ))
set @g = ' convert(varchar(10),time,120)'
if (datediff(d,@begintime,@endtime) > 365 )
set @g = ' convert(varchar(7),time,120)'
select @sql=isnull(@sql+',','')+'avg('+name+') as [avg'+name+']'
from syscolumns
where id=object_id('@sjb') and colid<=3
set @sql='select '+@sql+','+@g+' AS [time] from '+@sjb+' group by '+@g+''
exec(@sql)
end
GO
@begintime datetime,
@endtime datetime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
if (datediff(d,@begintime,@endtime) < 30 )
set @g = 'convert(varchar(13),time,120)'
if ((datediff(d,@begintime,@endtime) >= 30 ) and (datediff(d,@begintime,@endtime) <= 365 ))
set @g = ' convert(varchar(10),time,120)'
if (datediff(d,@begintime,@endtime) > 365 )
set @g = ' convert(varchar(7),time,120)' select @sql=isnull(@sql+',','')+'avg('+name+') as [avg'+name+']'
from syscolumns
where id=object_id(@sjb) and colid <=3 set @sql='select '+@sql+','+@g+' AS [time] from '+@sjb+' group by '+@g+''
exec(@sql)
end
GO