SQL Server雨情处理自动补数据

本文主要是介绍SQL Server雨情处理自动补数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在水利水雨情遥测数据的处理当中，一个ST_PPTN_R表中记录着各个站点在有降雨的情况下整点的累计雨量数据，而在没有降雨的情况下，是不往该表中写数据的。

在Android开发中，利用achartengine来绘制雨量柱状图的时候，要绘制时报、日报、月报，可以使用下面的语句进行

--时报
SELECT [TM],[DRP] FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-08-11 09:00' AND TM<='2014-08-12 08:00'  ORDER BY [TM];--日报
SELECT convert(varchar(10),tm,120) [TM],sum([DRP]) drp FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-08-01' AND TM<='2014-08-31' group BY convert(varchar(10),tm,120);--月报
SELECT convert(varchar(7),tm,120) [TM],sum([DRP]) drp FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-01-01' AND TM<='2014-12-31'  group BY convert(varchar(7),tm,120);

截图如下：

这样就存在一个问题，就是需要补时间，以下提供一种利用C#代码补非降雨时间：

if (context.Request.QueryString["cls"].ToString() == "sb")
{dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "sb");
}
if (context.Request.QueryString["cls"].ToString() == "rb")
{dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "rb");
}
if (context.Request.QueryString["cls"].ToString() == "yb")
{dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "yb");
}...#region  对缺少的日期进行自动补值 public DataTable FillNewDataTable(DataTable dt,string type){DataTable datatable = new DataTable();DataColumn dc = null;dc = datatable.Columns.Add("TM", Type.GetType("System.DateTime"));dc = datatable.Columns.Add("DRP", Type.GetType("System.Double"));switch (type){case "sb":for (DateTime d = BEGIN; d <= END; d=d.AddHours(1)){DataRow[] drv = dt.Select(" TM='" + d + "'");if (drv.Length == 0){DataRow dr = datatable.NewRow();dr["TM"] = d;dr["DRP"] = 0.0;datatable.Rows.Add(dr);}else if (drv.Length == 1){DataRow dr = datatable.NewRow();dr["TM"] = d;foreach (DataRow row in drv){dr["DRP"] =row[1];}datatable.Rows.Add(dr); }}break;case "rb":for (DateTime d = BEGIN; d <= END; d = d.AddDays(1)){DataRow[] drv = dt.Select(" TM='" + d.ToString("yyy-MM-dd") + "'");if (drv.Length == 0){DataRow dr = datatable.NewRow();dr["TM"] = d;dr["DRP"] = 0.0;datatable.Rows.Add(dr);}else if (drv.Length == 1){DataRow dr = datatable.NewRow();dr["TM"] = d;foreach (DataRow row in drv){dr["DRP"] = row[1];}datatable.Rows.Add(dr);}}break;case "yb":for (DateTime d = BEGIN; d <= END; d = d.AddMonths(1)){DataRow[] drv = dt.Select(" TM='" + d.ToString("yyyy-MM") + "'");if (drv.Length == 0){DataRow dr = datatable.NewRow();dr["TM"] = d;dr["DRP"] = 0.0;datatable.Rows.Add(dr);}else if (drv.Length == 1){DataRow dr = datatable.NewRow();dr["TM"] = d;foreach (DataRow row in drv){dr["DRP"] = row[1];}datatable.Rows.Add(dr);}}break; }return datatable;}#endregion
...

这篇关于SQL Server雨情处理自动补数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---