下面这段是原来用Access数据库时写的,现在想转成SQL Server的存储过程,各位达人帮看看。
if(ts.Days>1825)
{
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'yyyy') AS [ImportTime] FROM HisSimuData WHERE (ImportTime between #{0}# and #{1}#) and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyy') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData WHERE (ImportTime between #{0}# and #{1}#) and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyy'), Year([HisSimuData].[ImportTime]);",startTime,endTime,columns);
unit="年";
}
else
{
if(ts.Days>720)
{
unit="年季度";
sql1=String.Format("SELECT DISTINCT Format$(HisSimuData.ImportTime,'yyyyq') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyyq') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyyq'), Year([HisSimuData].[ImportTime])*4+DatePart('q',[HisSimuData].[ImportTime])-1; ",startTime,endTime,columns);
}
else
{
if(ts.Days>60)
{
unit="年月";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'yyyymm') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyymm') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyymm'), Year([HisSimuData].[ImportTime])*12+DatePart('m',[HisSimuData].[ImportTime])-1;",startTime,endTime,columns);
}
else
{
if(ts.Days>5)
{
unit="月日";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'MMDD') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW Format$([HisSimuData].[ImportTime],'MMDD') AS [ImportTime], HisSimuData.ID, Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY Format$([HisSimuData].[ImportTime],'MMDD'), HisSimuData.ID;",startTime,endTime,columns);
}
else
{
if(ts.Hours>5)
{
unit="日时";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'ddhh') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'ddhh') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'ddhh'), Year([HisSimuData].[ImportTime])*12+DatePart('m',[HisSimuData].[ImportTime])-1;",startTime,endTime,columns);
}
else
{
unit="时分";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'hhmm') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'hhmm') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY Format$([HisSimuData].[ImportTime],'hhmm'), HisSimuData.ID;",startTime,endTime,columns);
}
}
} 根据查询的开始时间startTime和结束时间endTime来返回数据集。时间间隔大于1825天,则返回以年为基准的数据集,时间间隔大于720天,则返回以季度为基准的数据集,时间间隔大于60天,则返回以月为基准的数据集,时间间隔大于5天,则返回以日为基准的数据集,时间间隔大于5小时,则返回以时为基准的数据集,小于5小时,则返回以分为基准的数据集 要求返回的数据集中有如下字段:realsimudata.id,realsimu.importtime,realsimudata.value 那个importtime可以转换成数字,所以月份和日都是两位数,不足两位数的十位补0
if(ts.Days>1825)
{
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'yyyy') AS [ImportTime] FROM HisSimuData WHERE (ImportTime between #{0}# and #{1}#) and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyy') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData WHERE (ImportTime between #{0}# and #{1}#) and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyy'), Year([HisSimuData].[ImportTime]);",startTime,endTime,columns);
unit="年";
}
else
{
if(ts.Days>720)
{
unit="年季度";
sql1=String.Format("SELECT DISTINCT Format$(HisSimuData.ImportTime,'yyyyq') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyyq') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyyq'), Year([HisSimuData].[ImportTime])*4+DatePart('q',[HisSimuData].[ImportTime])-1; ",startTime,endTime,columns);
}
else
{
if(ts.Days>60)
{
unit="年月";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'yyyymm') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyymm') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'yyyymm'), Year([HisSimuData].[ImportTime])*12+DatePart('m',[HisSimuData].[ImportTime])-1;",startTime,endTime,columns);
}
else
{
if(ts.Days>5)
{
unit="月日";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'MMDD') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW Format$([HisSimuData].[ImportTime],'MMDD') AS [ImportTime], HisSimuData.ID, Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY Format$([HisSimuData].[ImportTime],'MMDD'), HisSimuData.ID;",startTime,endTime,columns);
}
else
{
if(ts.Hours>5)
{
unit="日时";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'ddhh') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'ddhh') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY HisSimuData.ID, Format$([HisSimuData].[ImportTime],'ddhh'), Year([HisSimuData].[ImportTime])*12+DatePart('m',[HisSimuData].[ImportTime])-1;",startTime,endTime,columns);
}
else
{
unit="时分";
sql1=String.Format("SELECT DISTINCT Format$([HisSimuData].[ImportTime],'hhmm') AS [ImportTime] FROM HisSimuData WHERE ImportTime>#{0}# and ImportTime<#{1}# and id in ({2});",startTime,endTime,columns);
sql2=String.Format("SELECT DISTINCTROW HisSimuData.ID, Format$([HisSimuData].[ImportTime],'hhmm') AS [ImportTime], Max(HisSimuData.CurValue) AS [CurValue] FROM HisSimuData where ImportTime>#{0}# and ImportTime<#{1}# and id in ({2}) GROUP BY Format$([HisSimuData].[ImportTime],'hhmm'), HisSimuData.ID;",startTime,endTime,columns);
}
}
} 根据查询的开始时间startTime和结束时间endTime来返回数据集。时间间隔大于1825天,则返回以年为基准的数据集,时间间隔大于720天,则返回以季度为基准的数据集,时间间隔大于60天,则返回以月为基准的数据集,时间间隔大于5天,则返回以日为基准的数据集,时间间隔大于5小时,则返回以时为基准的数据集,小于5小时,则返回以分为基准的数据集 要求返回的数据集中有如下字段:realsimudata.id,realsimu.importtime,realsimudata.value 那个importtime可以转换成数字,所以月份和日都是两位数,不足两位数的十位补0
解决方案 »
- 学编程,找传智播客,嘿!大家进来看看
- 高手请进 帮个忙 关于mshtml的问题
- 江湖救急!一个比较简单的题目但我忘了怎么写了,请大家帮忙!
- 语音中的C#怎么在程序中设置识别为中文呢
- 本机能运行,上传后报错,请同志们指点~急啊~~~~~~~~~~~~
- 为什么在C#运行的时候看不到建成立的菜单啊?
- 请大家帮帮忙给一下实例 TCP实现获取远程计算机IP及端口号(支持多用户)
- 小妹是新手,问一个小问题,请指教
- winform问题 按钮点击后运行存储过程 数据填充到一个datatable内 操作完毕再点再fill(dt)出错
- 如何将邮件头中的各种日期格式都转换成我们常用的日期格式?在线等待中......
- socket问题,求助高手,解决后立即给分
- Asp.net中,如何获得上次访问页面的路径???????
你是需要代码还是不会写存储过程
代码这么常好像没人给你写,查查存储过程的语法应该很简单