首先,我想说我是SQL的新手,所以这似乎是一个愚蠢的问题。因此,在这段代码中,我收到一个Date作为参数,在其中添加61分钟并检查之间的值。然后,我对每一列的值求和并将其存储在另一个表中。该代码可以正常工作,我想知道的是,是否有更好的方法以及如何做到这一点(无需使用太多行或重复的代码)
提前致谢。
alter procedure Contagem
@date datetime
as
begin
declare
@Sala1 float,
@Sala2 float,
@Sala3 float,
...
@Sala26 float,
@Sala27 float,
@Sala28 float,
@dateplus datetime
set @Teste = 1
set @dateplus = (select DATEADD(MINUTE,61,@date))
set @Sala1 = (select sum(Sala_1_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala2 = (select sum(Sala_2_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala3 = (select sum(Sala_3_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
...
set @Sala26 = (select sum(Sala_26_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala27 = (select sum(Sala_27_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
set @Sala28 = (select sum(Sala_28_Energia) from Energia_15min where Time_Stamp between @date and @dateplus)
Insert into Custos_hora values (@date,@Sala1,@Sala2,@Sala3,@Sala4,@Sala5,@Sala6,@Sala7,@Sala8,@Sala9,@Sala10,@Sala11,@Sala12,@Sala13,@Sala14,@Sala15,@Sala16,@Sala17,@Sala18,@Sala19,@Sala20,@Sala21,@Sala22,@Sala23,@Sala24,@Sala25,@Sala26,@Sala27,@Sala28)
end
您可以一次击中该表,而不是像现在这样击中28次。
INSERT INTO Custos_hora
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)
FROM Energia_15min
WHERE Time_Stamp between @date and @dateplus
像这样声明您要插入的字段被认为是最佳实践。
INSERT INTO Custos_hora (Field1, Field2, Field3, Field4)
SELECT
SUM(Sala_1_Energia)
,SUM(Sala_2_Energia)
,SUM(Sala_3_Energia)
,SUM(Sala_4_Energia)
FROM Energia_15min
WHERE Time_Stamp between @date and @dateplus
另外,变量的作用@Teste
是什么?它似乎没有在任何地方使用。而且您似乎也没有宣布@date
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句