exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',2,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+'and DATEPART(day,dt)= '+@curD+'and DATEPART(hour,dt)>=08 and DATEPART(hour,dt)<=16 and tag='+@i ) DATEPART(hour,dt)>=08 and DATEPART(hour,dt)<=16 出有问题! 目的是从表中时间字段 取时间比较,满足时间才插入!!
--try 'and --> ' and
exec( 'insert into ' + 'DAY '+@curY+ ' select ' ' '+@dtday+ ' ' ' , '+@i+ ',2,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+ 'HOUR '+@curY+ ' where DATEPART(month,dt)= '+@curM+ ' and DATEPART(day,dt)= '+@curD+ ' and DATEPART(hour,dt) >=08 and DATEPART(hour,dt) <=16 and tag= '+@i )
set @sql = 'insert into ' + 'DAY '+@curY+ ' select ' ' '+@dtday+ ' ' ' , '+@i+ ',2,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+ 'HOUR '+@curY+ ' where DATEPART(month,dt)= '+@curM+ ' and DATEPART(day,dt)= '+@curD+ ' and DATEPART(hour,dt) >=08 and DATEPART(hour,dt) <=16 and tag= '+@i
print @sql --在执行前把语句打印出来看看
exec(@sql)
如果不行。楼主把数据和想要的结果贴出来吧。这样只能看到一点代码。不好调试噢
2、3、5 不可以执行!
set @i=1
while @i<=@tagnum
begin
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',1,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and DATEPART(hour,dt)<08 and DATEPART(hour,dt)>=01 and tag='+@i )
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',2,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and DATEPART(hour,dt)>=08 and DATEPART(hour,dt)<=16 and tag='+@i )
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',3,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and DATEPART(hour,dt)<24 and DATEPART(hour,dt)>=16 and tag='+@i )
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',4,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and DATEPART(hour,dt)<12 and DATEPART(hour,dt)>=0 and tag='+@i )
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',5,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and DATEPART(hour,dt)<24 and DATEPART(hour,dt)>=12 and tag='+@i )
exec('insert into ' + 'DAY'+@curY+ ' select '''+@dtday+''' ,'+@i+',6,AVG(mavg), MAX(mmax),MIN(mmin),AVG(mavg),sum(msum) FROM '+'HOUR'+@curY+' where DATEPART(month,dt)= '+@curM+' and DATEPART(day,dt)= '+@curD+' and tag='+@i )
set @i=@i+1
end
存表统计过程如下:string strconn = "user id=sa;data source=(local);persist security info=True;initial catalog=zsdb";
SqlConnection conn = new SqlConnection(strconn);
conn.Open();
System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("select * from HOUR2007 ",conn); // 这里的表名要与数据库里的表名一致
System.Data.SqlClient.SqlCommandBuilder cb = new SqlCommandBuilder(da);
System.Data.DataSet ds = new DataSet();
System.Data.DataTable dtab = new DataTable("HOUR2007");
//定义表结构
dtab.Columns.Add("dt",typeof(DateTime )); // char []可以写入到SQL数据库中对应的字符类型
dtab.Columns.Add("tag",typeof(int)); //real numeric SQL2000中float精度更高
dtab.Columns.Add("type",typeof(float));
dtab.Columns.Add("mval",typeof(float));
dtab.Columns.Add("mmax",typeof(float));
dtab.Columns.Add("mmin",typeof(float));
dtab.Columns.Add("mavg",typeof(float));
dtab.Columns.Add("msum",typeof(double));
ds.Tables.Add(dtab);
//----------产生数据开始时间
System.DateTime startTime = new System.DateTime();
System.DateTime startTime1 = DateTime.Now;
startTime = new DateTime(2007,2,1,1,1,1);
DateTime dt = startTime;
int oy=2007,om=2,od=0,y,m,d;
for(int i = 1;i < 600; i++) //i表示时间变化
{
y = dt.Year;
m =dt.Month;
d =dt.Day;
for( int j = 1; j< 10;j++) //j表示测点
{
DataRow dr = dtab.NewRow();
dr["dt"] = dt;
dr["tag"] = j ;
dr["type"] = 0;
dr["mval"] = i*6000+j;
dr["mmax"] = i*1000+j+.45;
dr["mmin"] = i*100+j+.05;
dr["mavg"] = i*500+j+.16;
dr["msum"] = 0;
dtab.Rows.Add(dr);
}
da.Update(ds,"HOUR2007"); //数据不能写入数据库,原因可能为数据库字段类型长度不够
ds.AcceptChanges();
ds.Tables[0].Rows.Clear();
if (d!=od)
{
//int mode = (y!=oy)?3:(m!=om)?2:1;
int mode = 1; System.Data.SqlClient.SqlCommand comm = new SqlCommand();
comm.CommandText="dbo.ZSTJ12";
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@bz",mode);
comm.Parameters.AddWithValue("@dt", dt);
comm.CommandTimeout = 1800;
try
{
if(conn.State==ConnectionState.Closed) conn.Open();
comm.ExecuteNonQuery();
}
catch(Exception E)
{
MessageBox.Show(E.ToString());
}
finally
{
comm.ResetCommandTimeout();
conn.Close();
}
}
oy = y;
om = m;
od = d;
dt = startTime.AddHours(i);
}
//----------数据产生完毕结束时间
DateTime dt2 = DateTime.Now;
TimeSpan diff1 = dt2.Subtract(startTime1);
MessageBox.Show("监听数据载入成功!耗时:" + diff1.TotalSeconds.ToString() + "秒");
conn.Close();
但出现1、4、6句可以执行
2、3、5 不可以执行!
估计 DATEPART(hour,dt) <=16
不能够比较!大家帮忙看看