SQL数据库表更新

Question

现有数据表Inv，按30秒采样；数据表DataInv按3分钟对Inv进行汇总。如何在Inv插入新数据时，对DataInv进行插入或者更新操作？
--创建如下数据表Inv
Create table Inv(
DTTime datetime primary key ,
ApPower  decimal(9,1),
Vac decimal(9,3), 
Iac decimal(9,3),
Pac decimal(9,1),
DE decimal(9,2),
NE decimal(9,2)
)
创建数据表，为Inv表数据按三分钟取值（有求和、平均值、最大值等）Create table DataInv(
DTTime datetime primary key ,
MaxApPowerTime datetime,
MaxApPower  decimal(9,1),
AvgApPower  decimal(9,1),
MaxVacTime datetime,
MaxVac decimal(9,3),
AvgVac decimal(9,3),
MaxIacTime datetime,
MaxIac decimal(9,3),
AvgIac decimal(9,3),
MaxPacTime datetime,
MaxPac decimal(9,1),
AvgPac decimal(9,1),
Pac decimal(9,1),
DE decimal(9,2),
NE decimal(9,2),
TodayDE decimal(9,2),
TodayNE decimal(9,2) 
)
--导入数据
Insert into Inv Values('2011-1-6 16:17:17','162','225.099','0.724','71','322.9',7.05)
Insert into Inv Values('2011-1-6 16:17:47','144','225.316','0.642','61','322.9',7.05)
Insert into Inv Values('2011-1-6 16:18:17','140','225.692','0.623','54','322.9',7.05)
Insert into Inv Values('2011-1-6 16:18:47','147','225.324','0.639','57','322.91',7.05)
Insert into Inv Values('2011-1-6 16:19:17','148','225.09','0.659','59','322.91',7.05)
Insert into Inv Values('2011-1-6 16:19:47','154','225.412','0.687','50','322.91',7.05)
由于现在只有6条数据 可以直接查询获取得到如下数据---查询获取三分钟数据
Select DTTime,MaxApPowerTime,AvgApPower,MaxVacTime,MaxVac,AvgVac,
MaxIacTime,MaxIac,AvgIac,MaxPacTime,MaxPac,AvgPac,Pac,DE,NE,TodayDN,TodayEN
from (
Select 1 as Sn,DTTime,Pac,DE,NE
from Inv 
where DTTime in (Select Max(DTTime) From Inv))as A,
( 
Select 1 as Sn,DTTime as MaxPacTime,Pac as MaxPac From Inv 
where Pac in (Select Max(Pac) From Inv)
) as B,
(Select 1 as Sn,Convert(decimal(9,1),Avg(ApPower)) as AvgApPower,
Convert(decimal(9,3),Avg(Vac)) as AvgVac,
Convert(decimal(9,3),Avg(Iac)) as AvgIac,
Convert(decimal(9,1),Avg(Pac)) as AvgPac
From  Inv) as C,
( 
Select 1 as Sn,DTTime as MaxVacTime,Vac as MaxVac From Inv 
where Vac in (Select Max(Vac) From Inv)
) as D,
( 
Select 1 as Sn,DTTime as MaxIacTime,Iac as MaxIac From Inv 
where Iac in (Select Max(Iac) From Inv)
) as E,
( 
Select 1 as Sn,DTTime as MaxApPowerTime,ApPower as MaxApPowerc From Inv 
where ApPower in (Select Max(ApPower) From Inv)
) as F,
(
Select 1 as Sn,Max(DE)-Min(DE) as TodayDN,Max(NE)-Min(NE) as TodayEN
From Inv
) as G
where A.Sn=B.Sn and B.Sn=C.Sn and C.Sn=D.Sn and D.Sn=E.Sn and E.Sn=F.Sn and F.Sn=G.Sn
由于现在仅有3分钟数据，做如下求和，查找最大值等操作后获得查询结果，将其插入DataInv，而实际应该是每3分钟做一次处理，将结果保存入DataInv
-------------------------------
Insert into DataInv 
Select DTTime,MaxApPowerTime,MaxApPower,AvgApPower,MaxVacTime,MaxVac,AvgVac,
MaxIacTime,MaxIac,AvgIac,MaxPacTime,MaxPac,AvgPac,Pac,DE,NE,TodayDN,TodayEN
from (
Select 1 as Sn,DTTime,Pac,DE,NE
from Inv 
where DTTime in (Select Max(DTTime) From Inv))as A,
( 
Select 1 as Sn,DTTime as MaxPacTime,Pac as MaxPac From Inv 
where Pac in (Select Max(Pac) From Inv)
) as B,
(Select 1 as Sn,Convert(decimal(9,1),Avg(ApPower)) as AvgApPower,
Convert(decimal(9,3),Avg(Vac)) as AvgVac,
Convert(decimal(9,3),Avg(Iac)) as AvgIac,
Convert(decimal(9,1),Avg(Pac)) as AvgPac
From  Inv) as C,
( 
Select 1 as Sn,DTTime as MaxVacTime,Vac as MaxVac From Inv 
where Vac in (Select Max(Vac) From Inv)
) as D,
( 
Select 1 as Sn,DTTime as MaxIacTime,Iac as MaxIac From Inv 
where Iac in (Select Max(Iac) From Inv)
) as E,
( 
Select 1 as Sn,DTTime as MaxApPowerTime,ApPower as MaxApPower From Inv 
where ApPower in (Select Max(ApPower) From Inv)
) as F,
(
Select 1 as Sn,Max(DE)-Min(DE) as TodayDN,Max(NE)-Min(NE) as TodayEN
From Inv
) as G
where A.Sn=B.Sn and B.Sn=C.Sn and C.Sn=D.Sn and D.Sn=E.Sn and E.Sn=F.Sn and F.Sn=G.Sn
 
---------------当有新数据插入表Inv时
Insert into Inv Values('2011-1-6 16:20:17','158','226.288','0.702','53','322.91',7.05)
如何同时对DataInv进行操作，数据库语句该如何写?
DataInv 的主键为DTTime  每条记录相差3分钟
 
（如何根据第一条记录开始时间，选取3分钟的间隔对DataInv进行插入或者更新操作，求各位帮忙，谢谢！）

Answer 1

触发器实现代码如下供参考.Ceeate TRIGGER tg_inv ON Inv
AFTER INSERT
AS 
BEGIN
  declare @InvDTTime datetime
  declare @DataInvDTTime datetime
  select @InvDTTime=DTTime from inserted  -- 获取当前插入Inv表的DTTime.
  select @DataInvDTTime=isnull(max(DTTime),'2011-01-06 00:00:00') 
       from DataInv -- 获取DataInv表的最大DTTime.
  if datediff(mi,@DataInvDTTime,@InvDTTime)>3  -- 如果最大DTTime小于当前DTTime 3分钟.
  begin
   Insert into DataInv  -- 插入DataInv表统计信息.
   Select top 1 DTTime,MaxApPowerTime,MaxApPower,AvgApPower,MaxVacTime,MaxVac,AvgVac,
    MaxIacTime,MaxIac,AvgIac,MaxPacTime,MaxPac,AvgPac,Pac,DE,NE,TodayDN,TodayEN
    from 
    (Select 1 as Sn,DTTime,Pac,DE,NE
     from Inv where DTTime in (Select Max(DTTime) From Inv))as A,
    (Select 1 as Sn,DTTime as MaxPacTime,Pac as MaxPac From Inv  
     where Pac in (Select Max(Pac) From Inv)) as B,
    (Select 1 as Sn,
      Convert(decimal(9,1),Avg(ApPower)) as AvgApPower,
      Convert(decimal(9,3),Avg(Vac)) as AvgVac,
      Convert(decimal(9,3),Avg(Iac)) as AvgIac,
      Convert(decimal(9,1),Avg(Pac)) as AvgPac From Inv) as C,
    (Select 1 as Sn,DTTime as MaxVacTime,Vac as MaxVac From Inv  
      where Vac in (Select Max(Vac) From Inv)) as D,
    (Select 1 as Sn,DTTime as MaxIacTime,Iac as MaxIac From Inv  
      where Iac in (Select Max(Iac) From Inv)) as E,
    (Select 1 as Sn,DTTime as MaxApPowerTime,ApPower as MaxApPower From Inv  
      where ApPower in (Select Max(ApPower) From Inv)) as F,
    (Select 1 as Sn,Max(DE)-Min(DE) as TodayDN,Max(NE)-Min(NE) as TodayEN
      From Inv) as G
    where A.Sn=B.Sn and B.Sn=C.Sn and C.Sn=D.Sn and D.Sn=E.Sn 
          and E.Sn=F.Sn and F.Sn=G.Sn
  endEND

Answer 2

不好意思,第1行打错,正确是: Create TRIGGER tg_inv ON Inv ...

Answer 3

谢谢！用触发器确实能满足一部分插入功能，但是实际上DataInv数据间隔不为3分钟，而且取最大值和平均值不在这3分钟的区间取得，辛苦ap0405140了。请删除原先数据
再提供如下插入语句可查看dataInv数据间隔，有的为3分半 有的为4分钟
Insert into Inv Values('2011-1-6 11:33:36','3416','228.351','14.964','3399','316.32',7.05)
Insert into Inv Values('2011-1-6 11:33:56','3371','228.381','14.763','3356','316.34',7.05)
Insert into Inv Values('2011-1-6 11:34:16','3474','229.466','15.141','3468','316.36',7.05)
Insert into Inv Values('2011-1-6 11:34:36','3428','229.268','14.955','3414','316.38',7.05)
Insert into Inv Values('2011-1-6 11:34:56','3512','229.673','15.295','3496','316.4',7.05)
Insert into Inv Values('2011-1-6 11:35:16','3549','230.218','15.419','3530','316.42',7.05)
Insert into Inv Values('2011-1-6 11:35:36','3466','230.226','15.058','3457','316.44',7.05)Insert into Inv Values('2011-1-6 11:35:56','3461','230.55','15.007','3447','316.46',7.05)
Insert into Inv Values('2011-1-6 11:36:16','3413','230.045','14.84','3399','316.47',7.05) 
Insert into Inv Values('2011-1-6 11:36:36','3313','230.009','14.408','3300','316.49',7.05)
Insert into Inv Values('2011-1-6 11:36:56','3330','230.159','14.473','3323','316.51',7.05)
Insert into Inv Values('2011-1-6 11:37:16','3256','230','14.158','3241','316.53',7.05)
Insert into Inv Values('2011-1-6 11:37:36','3337','228.802','14.586','3314','316.55',7.05)
Insert into Inv Values('2011-1-6 11:37:56','3343','229.171','14.592','3335','316.57',7.05)
Insert into Inv Values('2011-1-6 11:38:16','3390','228.691','14.807','3364','316.58',7.05)
Insert into Inv Values('2011-1-6 11:38:36','3327','228.518','14.548','3315','316.6',7.05)
Insert into Inv Values('2011-1-6 11:38:56','3390','228.728','14.825','3377','316.62',7.05)

Answer 4

楼主的取样间隔由30秒变20秒了?如要需要再精确的控制,请修改触发器中
datediff(ss,@DataInvDTTime,@InvDTTime) --获取2个时间相差的秒数来判断
触发器只是一个框架,细节部分请需楼主完善,毕竟我也不熟楼主的业务需求.