触发器(七)Trigger_Insert_VocDAY

最新推荐文章于 2023-12-30 18:46:47 发布

u010082526

最新推荐文章于 2023-12-30 18:46:47 发布

阅读量270

点赞数

CC 4.0 BY-SA版权

分类专栏： sqlserver

本文链接：https://blog.youkuaiyun.com/u010082526/article/details/78894705

sqlserver 专栏收录该内容

58 篇文章

订阅专栏

本文介绍了一个SQL Server触发器，该触发器用于从每小时数据表[T_Data_VocHour]中计算并插入日平均数据到[T_Data_VocDay]表。触发器通过计算指定站点的每小时平均值来生成日数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [AirDB_YiZhuang]
GO

/****** Object: Trigger [dbo].[Trigger_Insert_VocDAY] Script Date: 2017/12/25 17:06:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: pyy
-- Create date: 2016-10-8
-- Description: Vochour数据求平均得到日数据
---- mondify Description: 2017-4-5站点“宾士国际”的数据只更新到了3-28
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_VocDAY]
ON [dbo].[T_Data_VocHour]
AFTER INSERT--,DELETE,UPDATE
AS
BEGIN
declare @t1 datetime
declare @t2 datetime
declare @Hourt datetime
--站点1的最新时间
set @t1=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_VocHour] where STATIONCODE= '500000001')
--之前站点编号写错了“5000001”，应该是“500000001”
--站点2的最新时间
set @t2=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_VocHour] where STATIONCODE= '500000002')
--之前站点编号写错了“5000002”，应该是“500000002”
--最新时间里的最小值
set @Hourt=@t1
--(select case when @t1<@t2 then @t1 else @t2 end)
insert into [AirDB_YiZhuang].[dbo].[T_Data_VocDay]
(STATIONCODE,MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane)
select STATIONCODE,cast(MONIDATE as datetime) MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane
from
(select STATIONCODE,
substring(convert(varchar,MONIDATE,120),0,11) MONIDATE,
AVG(Vinyl_chloride) Vinyl_chloride,AVG(Butadiene) Butadiene,
AVG(Ethylene) Ethylene,AVG(ethane) ethane,
AVG(Propane) Propane,AVG(propylene) propylene,
AVG(butylene) butylene,AVG(twoButene) twoButene,
AVG(cistwoButene) cistwoButene,AVG(transtwoPentene) transtwoPentene,
AVG(cistwoPentene) cistwoPentene,AVG(onePentene) onePentene,
AVG(Isoprene) Isoprene,AVG(IsoButene) IsoButene,
AVG(nbutane) nbutane,AVG(Isopentane) Isopentane,
AVG(nPentane) nPentane,AVG(dimethylbutane) dimethylbutane,
AVG(symDichloroethane) symDichloroethane,AVG(Methylpentane) Methylpentane,

AVG(nHexane) nHexane,AVG(Benzene) Benzene,
AVG(cyclohexane) cyclohexane,AVG(nHeptane) nHeptane,
AVG(octane) octane,AVG(nNonane) nNonane,
AVG(Cumene) Cumene,AVG(nPropylbenzene) nPropylbenzene,
AVG(trimethylbenzene) trimethylbenzene,AVG(Mesitylene) Mesitylene,
AVG(Chlorobenzene) Chlorobenzene,AVG(jiandichlorobenzene) jiandichlorobenzene,
AVG(duidichlorobenzene) duidichlorobenzene,AVG(lindichlorobenzene) lindichlorobenzene,
AVG(tetrachloroethylene) tetrachloroethylene,AVG(trichloroethylene) trichloroethylene,
AVG(cyclopentane) cyclopentane,AVG(decane) decane,
AVG(Diethylbenzene) Diethylbenzene,AVG(duiDiethylbenzene) duiDiethylbenzene,

AVG(ttdimethylpentane) ttdimethylpentane,AVG(tfdimethylpentane) tfdimethylpentane,
AVG(methylcyclopentane) methylcyclopentane,AVG(methylheptane) methylheptane,
AVG(methylhexane) methylhexane,AVG(trimethylpentane) trimethylpentane,
AVG(undecane) undecane
from
[dbo].[T_Data_VocHour]
where MONIDATE >=(select DATEADD(DAY,1,max(MONIDATE)) from T_Data_VocDay)
--T_Data_VocDay最新的时间
and
--MONIDATE <(select substring(convert(varchar,max(monidate),120),0,12)+'00:00:00' from T_Data_VocMin)
--T_Data_VocHour最新的时间的日期值
--当两个站点的小时数据不同步的时候,数据慢的那个站点的日平均数据就可能不对(不足24条小时数据)
MONIDATE <(substring(convert(varchar,@Hourt,120),0,12)+'00:00:00')
group by STATIONCODE,substring(convert(varchar,MONIDATE,120),0,11)) as a

END

GO