|
来源：老董聊电气自动化（头条）组态软件要往数据库里存数据，各个品牌的实现方法不同，但是其实实现的思路基本相同，而且所有组态软件都有这个功能！我就不讲怎么存的了，根据组态软件不同，各位自己去搜索如：“WINCC存数据到SqlServer","InTouch存数据到SqlServer"等等。。 今天我们主要讲数据库的规划以及将数据存储进数据库后如何展示出来和导出成为Excel或PDF的一种实现方法。另外集合了一些SqlServer，RDLC的知识点。 文章很长，建议收藏，转发、点赞~效果图如下，如果做上位机相信会用到：
一、数据库规划打开数据库，建立各种表： 
1、变量表建立一个TAGDESC表，用于存放需要的变量，TAGID用于与其它表映射关系，TAGNAME用于标示变量名称，TAGDESC用于存放变量注释，Locate用于存放归属位置。
这个表是自己建的，用于存放注释，以及变量名和ID对应值。以及Locate的区域注释。 2、数值记录表
建立一个数值记录表示例中的表名qd_data_temp,记录时间，记录值，区域值，后面会将ID和变量映射、区域和区域注释映射。 3、区域对应表
本例中不使用
二、VS——RDLC报表建立，报表展示方式
打开SQLServerBusinessIntelligence 
1、新建项目
２、选择数据源
３、定义数据集
数据集选项 SELECT N.Tagid_d AS 序号, T.TagDesc AS 注释, CONVERT(VARCHAR, N.Sampletime_d, 111) AS 日期, DATEPART(hh, N.Sampletime_d) AS 时间, AVG(N.Samplevalue_d) AS 值 FROM qd_data_temp AS N INNER JOIN TagDesc AS T ON N.Tagid_d = T.TagID WHERE (CONVERT(VARCHAR, N.Sampletime_d, 111) = CONVERT(VARCHAR, @查询日期, 111)) GROUP BY N.Tagid_d, T.TagDesc, CONVERT(VARCHAR, N.Sampletime_d, 111), DATEPART(hh, N.Sampletime_d) ORDER BY 日期, 序号 （简单方式
SELECT N.Tagid_d AS 序号, N.Sampletime_d, N.Samplevalue_d, T.TagDesc as 注释 FROM qd_data_temp AS N INNER JOIN TagDesc AS T ON N.Tagid_d = T.TagID
）
SQL语句说明： SELECT 表代号.表列名 AS 列别名...... FROM 数据表1名 AS 表代号 INNER JOIN 数据表2名AS表代号 表1.关联项=表2.关联项 选择矩阵 ４、数据展示样式
下一步，直到完成 ５、参数设置设置参数数据类型为日期型。
６、最终效果效果如下，可以导出为ＥＸＣＥＬ,也可以是导出 PDF
三、ＳＱＬ常用语句１、其他可用语句：SQL SELECT INTO 选取一部分创建一个新表 实例： SELECT A表.列，B表.列 INTO 新表名 FROM A表 INNER JOIN 表1 ON 表1.列=表2.列 WHERE 条件
２、视图的使用CREATE VIEW CREATE VIEW DATATEST AS SELECT N.Tagid_d AS 序号, N.Sampletime_d, N.Samplevalue_d, T.TagDesc AS 注释 FROM qd_data_temp AS N INNER JOIN TagDesc AS T ON N.Tagid_d = T.TagID 视图可以像表一样查询 SELECT * FROM [DATATEST] 删除视图 SQL DROP VIEW [DATATEST]
３、时间函数
NOW()返回当前日期和时间 CURDATE()返回房前日期时间单独部分 dateadd()日期时间加法 datediff()返回两个日期之间的时间 convert()用不同的格式显示日期时间
４、分组按小时分组平均值 SELECT year(日期时间列名），ＭＯＮＴＨ(日期时间列名），ＤＡＹ(日期时间列名），ＤＡＴＥＰＡＲＴ（ＨＯＵＲ，(日期时间列名），ＡＶＧ（值列) ＦＲＯＭ 表名 ＧＲＯＵＰ ＢＹ year(日期时间列名），ＭＯＮＴＨ(日期时间列名），ＤＡＹ(日期时间列名），ＤＡＴＥＰＡＲＴ（ＨＯＵＲ，(日期时间列名）
select 序号,CONVERT(VARCHAR,Sampletime_d,111) as 日期,DATEPART(hh,Sampletime_d) as 时 ,AVG(Samplevalue_d) as 值 from DATATEST WHERE CONVERT(VARCHAR,Sampletime_d,111)='2019/10/28' group by 序号,CONVERT(VARCHAR,Sampletime_d,111),DATEPART(hh,Sampletime_d) order by 日期,序号 先创建视图
CREATE VIEW DATATEST AS SELECT N.Tagid_d AS 序号, N.Sampletime_d, N.Samplevalue_d, T.TagDesc AS 注释 FROM qd_data_temp AS N INNER JOIN TagDesc AS T ON N.Tagid_d = T.TagID
再查询
select 序号,注释,CONVERT(VARCHAR,Sampletime_d,111) as 日期,DATEPART(hh,Sampletime_d) as 时 ,AVG(Samplevalue_d) as 值 from DATATEST WHERE Sampletime_d between '2019-5-6' AND '2019-10-29' group by 序号,释,CONVERT(VARCHAR,Sampletime_d,111),DATEPART(hh,Sampletime_d) order by 日期,序号
时间间隔查询方式
select 序号,注释,CONVERT(VARCHAR,Sampletime_d,111) as 日期,DATEPART(hh,Sampletime_d) as 时 ,AVG(Samplevalue_d) as 值 from DATATEST WHERE Sampletime_d between '2019-5-6' AND '2019-10-29' group by 序号,释,CONVERT(VARCHAR,Sampletime_d,111),DATEPART(hh,Sampletime_d) order by 日期,序号 四、RDLC查询报表的其他示例１、全厂数据报表１、展示
２、框架
３、查询开始时间参数
４、时间间隔参数
５、车间选择参数
６、变量选择参数
７、ＰＶＮＡＭＥＳ参数=join(Parameters!变量选择.Value,";") 标签和值相同
８、ＤＡＴＡＳＥＴ１脚本declare @StartTime bigint declare @EndTime bigint declare @intervalTime bigint declare @TagsID Varchar(1000) declare @st Varchar(1000) declare @temp1 Varchar(1000) declare @temp2 Varchar(1000) declare @temp3 Varchar(1000) declare @temp4 Varchar(1000)
set @temp1=CONVERT(Varchar(1000),@开始日期, 23); set @st =' '+@查询开始时间; set @temp2 =convert(datetime,@temp1+@st); set @StartTime =dbo.ToBigInt(convert(datetime,@temp2));
set @temp3=CONVERT(Varchar(1000),@结束日期, 23); set @st =' '+@查询结束时间; set @temp4 =convert(datetime,@temp3+@st); set @EndTime =dbo.ToBigInt(convert(datetime,@temp4));
set @intervalTime =dbo.IntervalToBigInt(@时间间隔); set @tagsID=dbo.CTTagnamesToTagIDs(@pvnames); select dbo.TagDesc.TagDesc,dbo.ToDate(SampleDateTime) AS SampleDateTime,cast(SampleValue as decimal(10,1)) as SampleValue from fn_get_samples_matrix_byinterval(@TagsID,@StartTime,@EndTime,0,@intervalTime) WA INNER JOIN dbo.TagDesc ON TagDesc.TagID=WA.TagID ９、ＤＡＴＡＳＥＴ２脚本select TagID,TagName,TagDesc from TagDesc where Locate=@车间选择 １０、ＤＡＴＡＳＥＴ３脚本select chejian from qd_chejian_desc where Locate=@车间选择 ２、车间数据报表
use [sql] declare @temp1 varchar(50) declare @temp2 varchar(100) declare @temp3 varchar(300) declare @temp4 varchar(100) declare @temp5 varchar(100) declare @temp6 varchar(200) declare @TagsID Varchar(2000) if (LEN(CAST(month(@查询日期) as char(100)))>1) begin set @temp4=CAST(month(@查询日期) AS char(100)) end else begin set @temp4='0'+CAST(month(@查询日期) AS char(100)) end if (LEN(CAST(day(@查询日期) as char(100)))>1) begin set @temp5=CAST(day(@查询日期) AS char(100)) end else begin set @temp5='0'+CAST(day(@查询日期) AS char(100)) end set @temp2='qd_ymd'+convert(varchar(100),year(@查询日期))+rtrim(@temp4)+ltrim(@temp5) set @temp3='select TagDesc_d AS TagDesc,Sampletime_d AS 时间,Samplevalue_d AS 数值 from '+ @temp2 set @temp4=' where Locate_d='''+@车间选择+''' and Jiezhi_d='''+@介质选择+''' and Pici_d='''+@计量参数+''' order by Sampletime_d' EXEC(@temp3+@temp4) 
３、一种饼图展示 USE [SQL] declare @temp1 varchar(50) declare @temp2 varchar(100) declare @temp3 varchar(300) declare @temp4 varchar(300) declare @temp5 varchar(300) declare @temp6 varchar(300) declare @temp7 Varchar(2000) if (LEN(CAST(month(@月份选择) as char(100)))>1) begin set @temp4=CAST(month(@月份选择) AS char(100)) end else begin set @temp4='0'+CAST(month(@月份选择) AS char(100)) end set @temp2='qd_ym'+convert(varchar(100),year(@月份选择))+rtrim(@temp4) set @temp6=convert(varchar(100),year(@月份选择))+'年'+convert(varchar(100),month(@月份选择))+'月 高温水热量月用量汇总 单位：GJ' set @temp3='select Locate_m ,qd_chejian_desc.chejian,SUM(Samplevalue_m) as total ,'''+@temp6+'''as yuefen from '+ @temp2 set @temp4=' INNER join qd_chejian_desc on ' + @temp2+'.Locate_m =qd_chejian_desc.Locate where ' set @temp5='Jiezhi_m=''3'' and Pici_m=''2'' group by '+@temp2 +'.Locate_m,qd_chejian_desc.chejian' exec(@temp3+@temp4+@temp5) 
五、利用SQL作业进行创建，修改，删除等作业
１、创建作业右键单击作业，选新建作业
２、修改名称等信息，点击步骤，创建脚本，以下脚本可以创建一个日报表
USE [SQL] declare @temp1 varchar(100) declare @temp2 varchar(100) declare @temp3 varchar(100) declare @temp4 varchar(80) declare @createtable varchar(500) set @temp1='qd_ymd'+CONVERT(Varchar(100),getdate(),112) IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp1 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp1+'(Tagid_d int,TagDesc_d nvarchar(3000),Locate_d nvarchar(100),Sampletime_d datetime,Samplevalue_d float,Jiezhi_d nvarchar(50),Pici_d nvarchar(50))' exec(@createtable) set @temp2='CREATE INDEX Tagid_d on '+@temp1+'(Tagid_d)' exec(@temp2) end ３、设置计划运行时间
４、脚本参考１、创建日报表USE [SQL] declare @temp1 varchar(100) declare @temp2 varchar(100) declare @temp3 varchar(100) declare @temp4 varchar(80) declare @createtable varchar(500)
set @temp1='qd_ymd'+CONVERT(Varchar(100),getdate(),112) IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp1 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp1+'(Tagid_d int,TagDesc_d nvarchar(3000),Locate_d nvarchar(100),Sampletime_d datetime,Samplevalue_d float,Jiezhi_d nvarchar(50),Pici_d nvarchar(50))' exec(@createtable)
set @temp2='CREATE INDEX Tagid_d on '+@temp1+'(Tagid_d)' exec(@temp2) end ２、创建月报表USE [SQL] declare @temp1 varchar(50) declare @temp2 varchar(50) declare @temp3 varchar(50) declare @temp4 varchar(100) declare @createtable varchar(500)
if (LEN(CAST(month(GETDATE()) as char(100)))>1) begin set @temp1=CAST(month(GETDATE()) AS char(100)) end else begin set @temp1='0'+CAST(month(GETDATE()) AS char(100)) end set @temp2= CAST(year(GETDATE())as char(100)); set @temp3 ='qd_ym'+rtrim(@temp2)+ltrim(@temp1)
IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp3 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp3+'(Tagid_m int,TagDesc_m nvarchar(3000),Locate_m nvarchar(100),Sampletime_m datetime,Samplevalue_m float,Jiezhi_m nvarchar(50), Pici_m nvarchar(50))' exec(@createtable) set @temp4='CREATE INDEX Tagid_m on '+@temp3+'(Tagid_m)' exec(@temp4) end ３、汇总日报表数据到月报表USE [SQL] declare @temp1 varchar(50) declare @temp2 varchar(50) declare @temp3 varchar(100) declare @temp4 varchar(100) declare @temp5 varchar(80) declare @temp6 varchar(200) declare @temp7 varchar(100) declare @temp8 varchar(50) declare @temp9 varchar(50) declare @temp10 varchar(50) declare @temp11 varchar(100) declare @temp12 varchar(100) declare @temp13 varchar(1000) declare @temp14 varchar(500) declare @createtable varchar(500)
/*生成昨天的月份*/ if (LEN(CAST(month(dateadd(dd,-1, GETDATE()) )as char(100)))>1) begin set @temp1= CAST(month(dateadd(dd,-1, GETDATE()))AS char(100)) end else begin set @temp1='0'+ CAST(month(dateadd(dd,-1, GETDATE()))AS char(100)) end set @temp2= CAST(year(dateadd(dd,-1, GETDATE()) )as char(100)); set @temp3 ='qd_ym'+rtrim(@temp2)+ltrim(@temp1) /*判断月报表格是否存在*/ IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp3 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp3+'(Tagid_m int,TagDesc_m nvarchar(3000),Locate_m nvarchar(100),Sampletime_m datetime,Samplevalue_m float, Jiezhi_m nvarchar(50), Pici_m nvarchar(50))' exec(@createtable) set @temp4='CREATE INDEX Tagid_m on '+@temp3+'(Tagid_m)' exec(@temp4) end /*汇总昨日总量到月报*/ /*判断日报是否存在*/ set @temp6='qd_ymd'+ CONVERT(Varchar(1000),dateadd(dd,-1,getdate()),112) IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp6 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp6+'(Tagid_d int,TagDesc_d nvarchar(2000),Locate_d nvarchar(100),Sampletime_d datetime,Samplevalue_d float, Jiezhi_d nvarchar(50), Pici_d nvarchar(50))' exec(@createtable) set @temp7='CREATE INDEX Tagid_d on '+@temp6+'(Tagid_d)' exec(@temp7) end
/*插入昨日的汇总数据*/ set @temp8=year(dateadd(dd,-1,getdate())) set @temp9=month(dateadd(dd,-1,getdate())) set @temp10=day(dateadd(dd,-1,getdate())) set @temp11=CONVERT(Varchar(100), dateadd(dd,-1,getdate()),112) set @temp12=CONVERT(varchar(50),year(@temp11))+'-'+convert(varchar(50),month(@temp11))+'-'+convert(varchar(50),day(@temp11)) set @temp13='insert into '+@temp3+'(Tagid_m,TagDesc_m,Locate_m,Sampletime_m,Samplevalue_m, Jiezhi_m,Pici_m) select Tagid_d, TagDesc.TagDesc, TagDesc.Locate,'''+@temp12+''',Samplevalue_d, TagDesc.Jiezhi, TagDesc.Pici from (select Tagid_d,cast(sum(Samplevalue_d) as decimal(10,1)) as Samplevalue_d from '+@temp6+' w where year(Sampletime_d)='+ @temp8+' and month(Sampletime_d)='+ @temp9+' and day(Sampletime_d)='+ @temp10+' group by Tagid_d ) a inner join TagDesc on TagDesc.TagID=a.Tagid_d' exec(@temp13) /*把昨天的历史数据清除*/ Set @temp14='DELETE from qd_data_temp where year(Sampletime_d)='+ @temp8+' and month(Sampletime_d)='+ @temp9+' and day(Sampletime_d)='+ @temp10+ '' exec(@temp14) ４、日报表小时数据计算存入说明：需要每小时建立一个作业。 USE [SQL] /* 23点的*/ declare @temp1 varchar(100) declare @temp2 varchar(100) declare @temp3 varchar(200) declare @temp4 varchar(100) declare @temp5 varchar(100) declare @temp6 datetime declare @temp7 Varchar(100) declare @temp8 Varchar(1000) declare @temp9 varchar(1000) declare @temp10 varchar(1000) declare @temp11 datetime declare @temp12 datetime declare @temp13 varchar(100) declare @temp14 varchar(1000) declare @temp15 varchar(1000) declare @temp16 varchar(100) declare @temp17 varchar(100)
declare @output varchar(1000); declare @createtable varchar(500) declare @StartTime bigint declare @EndTime bigint declare @intervalTime bigint declare @TagsID Varchar(2000) declare @st Varchar(100) /*把昨天的23点的数据和今天的0:0:05前的数据写入qd_date_temp */ set @temp17=CONVERT(Varchar(100), dateadd(dd,-1,getdate()), 23); set @temp1=CONVERT(Varchar(100), getdate() , 23); set @temp2=@temp17+' '+ '23:00:00:00'; set @temp13=@temp1+' '+ '00:00:00:00'; set @StartTime=dbo.ToBigInt(@temp2); set @EndTime =dbo.ToBigInt(@temp13); set @intervalTime =dbo.IntervalToBigInt('30m');
select @TagsID =coalesce(@TagsID,'')+ cast(TagID as varchar(10))+';' from TagDesc insert into dbo. qd_data_temp(Tagid_d,Sampletime_d, Samplevalue_d ,Locate) select w.TagID,dbo.ToDate(w.SampleDateTime), convert(float,w.SampleValue) as SampleValue ,TagDesc.Locate FROM (select TagID,SampleDateTime,SampleValue,QualityID from fn_get_samples_matrix_byinterval (@TagsID,@StartTime,@EndTime,0,@intervalTime)) w inner join TagDesc on w.TagID=TagDesc.TagID where (QualityID=192 and TagDesc.Locate<>'shui' and year(dbo.ToDate(SampleDateTime))= year(dateadd(dd,-1,getdate())) and month(dbo.ToDate(SampleDateTime))= month(dateadd(dd,-1,getdate())) and day(dbo.ToDate(SampleDateTime))= day(dateadd(dd,-1,getdate())) and datepart(hh,dbo.ToDate(SampleDateTime))=23) or (QualityID=192 and TagDesc.Locate<>'shui'and year(dbo.ToDate(SampleDateTime))= year(getdate()) and month(dbo.ToDate(SampleDateTime))= month(getdate()) and day(dbo.ToDate(SampleDateTime))= day(getdate()))
/*判断前一天的日报是否存在,没有建立*/
set @temp3='qd_ymd'+CONVERT(Varchar(1000),dateadd(dd,-1,getdate()),112) IF not EXISTS (SELECT name FROM sysobjects WHERE name =@temp3 AND type = 'U') begin set @createtable='CREATE TABLE '+@temp3+'(Tagid_d int,TagDesc_d nvarchar(3000),Locate_d nvarchar(100),Sampletime_d datetime,Samplevalue_d float, Jiezhi_d nvarchar(50), Pici_d
nvarchar(50))' exec(@createtable) set @temp4='CREATE INDEX Tagid_d on '+@temp3+'(Tagid_d)' exec(@temp4) end
/*本小时数据整理，插入日报数据为前一小时数据和当前小时的 #：05:00数据*/ set @temp5=CONVERT(Varchar(100), getdate() , 20); set @temp6=dateadd(dd,-1,convert(datetime,@temp5));
set @temp9='23:00:00' set @temp10=CONVERT(varchar(100),year(@temp6))+'-'+convert(varchar(1000),month(@temp6))+'-'+convert(varchar(1000),day(@temp6))+' '+@temp9 set @temp11=convert(datetime,@temp10,20)
/*插入日报数据, id大小写规范,添加了一个取值不为0的情况,避免差值太大*/ set @temp14='insert into '+@temp3+'(Tagid_d,TagDesc_d,Locate_d,Jiezhi_d , Pici_d,Sampletime_d,Samplevalue_d) select Tagid_d,TagDesc.TagDesc,TagDesc.Locate, TagDesc.Jiezhi, TagDesc.Pici,'''+@temp10+''' as Sampletime_d,Samplevalue_d from ( select Tagid_d,cast(max(Samplevalue_d)-min(Samplevalue_d) as decimal(10,1)) as Samplevalue_d from qd_data_temp w where(datepart(hh,w.Sampletime_d)=23 and day(Sampletime_d)=day(dateadd(dd,-1,getdate())) and month(w.Sampletime_d)=month(dateadd(dd,-1,getdate())) and Samplevalue_d<>0) or (day(w.Sampletime_d)=day(getdate()) and month(w.Sampletime_d)=month(getdate()) and (datepart(hh,w.Sampletime_d)=0) and Samplevalue_d<>0) group by Tagid_d ) a inner join TagDesc on TagDesc.TagID=a.Tagid_d' exec(@temp14) ５、自动删除过期数据use [sql] declare @tb varchar(100) declare @tb1 varchar(100)
/* 删除日报 */ while(EXISTS (SELECT name FROM sysobjects WHERE type = 'U' AND name LIKE 'qd_ymd%' and datediff( day,convert(datetime,RIGHT(name,8),120),getdate())>=60)) BEGIN SELECT @tb='drop table '+name FROM sysobjects WHERE type = 'U' AND name LIKE 'qd_ymd%' and datediff( day,convert(datetime,RIGHT(name,8),120),getdate())>=60 exec(@tb)
END
/* 删除月报 */
while(EXISTS (SELECT name FROM sysobjects WHERE type = 'U' AND name LIKE 'qd_ym2%' and datediff(month,convert(datetime,RIGHT(name,6)+'01',120),getdate())>=12)) BEGIN SELECT @tb1='drop table '+name FROM sysobjects WHERE type = 'U' AND name LIKE 'qd_ym2%' and datediff(month,convert(datetime,RIGHT(name,6)+'01',120),getdate())>=12 exec(@tb1)
END
/* 删除numsample, */
delete from NumericSamples where dbo.ToDate(SampleDateTime)<= dateadd(day,-60,getdate())
| 
/1 
|手机版|免责声明|本站介绍|工控课堂
( 沪ICP备20008691号-1 )
发表于 2021-1-9 19:28:08
提升卡
置顶卡
照妖镜
发表于 2021-1-9 19:28:09