将第一个表的平均值保存到第二个表中，并在保存后删除第一个表中的数据 [英] Save average of value from first table into second table and delete the data in first table after the saving

问题描述

嗨...

我有两张桌子，当第一张表中的传感器值> 0时，以及当传感器值= 0时，我希望从传感器保存值我想先计算平均传感器值表并将resulat保存在第二个表中并删除vb.net第一个表中的数据



我尝试过：

hi...
I have two tables and I want save value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net

What I have tried:

hi...
I have two tables and I want save  value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net

推荐答案

如果您是通过SQL Server执行此操作;最好的选择可能是利用存储过程来执行逻辑和后续操作。



如果没有您的更多具体信息，我想出了一个概念，满足您的需求。

它有一些额外的项目：

- 使用多个传感器的能力

- 跟踪使用Table1时的个人时间

- 聚合传感器时间，以便在表2中获得范围（时间跨度）。



从未完成项目...你确实需要为零度以下的读数提出行动计划。您可以在第二个表中添加读取计数列

If you are doing this via an SQL Server; the best option would probably be to utilize a Stored Procedure to do the logic and subsequent actions.

Without more specific information from you, I came up with a concept which will meet your needs.
It has a few additional items:
- Ability to use multiple sensors
- Tracks the individual time while using Table1
- Aggregates the sensor times so that you will have the range (time span) in table2.

Never a finished project... you do need to come up with an action plan for sub-zero readings. You could add a "Reading Count" column to the second table as well

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue INT,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
	SensorValue      INT,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
	@SensorID     INT,
	@SensorValue  INT 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID

		DELETE Table1	WHERE SensorID = @SensorID

	END; ELSE BEGIN
		-- ToDo: What happens if negative value passed in
		Print 'Some error occurred......'
	END
END
GO

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue Decimal,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO




CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
        period          Int,
	SensorValue     Decimal,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
@SensorID     INT,
	@SensorValue  Decimal 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd , period,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),DATEDIFF(s,Min(SensorTime),Max(SensorTime)),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID group by SensorID

		truncate table Table1

END; 
END

这篇关于将第一个表的平均值保存到第二个表中，并在保存后删除第一个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！