sql server中的最小函数工作不正常，给出0

Question

我想从今天的最小值减去今天的最大值。im正确地获得了最大值，但最小值为0。如何从kvarh的最大值减去今天的最小值。

数据类型kvarh-float，设备时间戳-日期时间

代码

select(
SELECT MAX(kvarh)
FROM [Transformer].[dbo].[Total_Power]
WHERe DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996')
-
(SELECT MIN(kvarh)
FROM [Transformer].[dbo].[Total_Power]
WHERe DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996')
as Difference

样本数据

A_id   DeviceImei  DeviceTimeStamp           KWH       KVARH       
985302  868996     2020-10-16 10:30:30.000  36682.5     424107.1    
985298  868996     2020-10-16 10:15:31.000  36678.94    424103.3    
985296  8689960    2020-10-16 10:00:29.000  36675.88    424099.8    

Answer 1

我已经检查了您的查询，它运行良好。你可以像这样减少一点：

DECLARE @DataSource TABLE
(
[A_id] INT  
,[DeviceImei]  INT
,[DeviceTimeStamp] DATETIME
,[KWH] DECIMAL(9,2)
,[KVARH] DECIMAL(9,2)
);
INSERT INTO @DataSource ([A_id], [DeviceImei], [DeviceTimeStamp], [KWH], [KVARH])
VALUES (985302, 868996, '2020-10-16 00:30:30.000', 36682.5, 424107.1)
,(985298, 868996, '2020-10-16 00:15:31.000', 36678.94, 424103.3)
,(985296, 8689960, '2020-10-16 00:00:29.000', 36675.88, 424099.8);

SELECT MAX(kvarh) - MIN(kvarh) AS Difference
FROM @DataSource
WHERE DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996';

MIN函数不会返回带有此示例数据的0——如果找不到行，则会得到NULL。只有当只有一行符合您的条件时，您才会得到0，因为您将减去相同的值。

例如：

DECLARE @DataSource TABLE
(
[A_id] INT  
,[DeviceImei]  INT
,[DeviceTimeStamp] DATETIME
,[KWH] DECIMAL(9,2)
,[KVARH] DECIMAL(9,2)
);
INSERT INTO @DataSource ([A_id], [DeviceImei], [DeviceTimeStamp], [KWH], [KVARH])
VALUES (985302, 868996, '2020-10-16 00:30:30.000', 36682.5, 424107.1)
--,(985298, 868996, '2020-10-16 00:15:31.000', 36678.94, 424103.3)
,(985296, 8689960, '2020-10-16 00:00:29.000', 36675.88, 424099.8);

SELECT MAX(kvarh) - MIN(kvarh) AS Difference
FROM @DataSource
WHERE DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996';

Answer 2

select(
select max(KVARH) from [Transformer].[dbo].[Total_Power] 
where cast(DeviceTimeStamp as Date)  = cast(getdate() as Date) and DEVICEIMEI = '868996'
)
-
(
select min(KVARH) from [Transformer].[dbo].[Total_Power] 
where cast(DeviceTimeStamp as Date)  = cast(getdate() as Date) and DEVICEIMEI = '868996'
)
as Todays_Consumption

Answer 3

Qu您的最小子查询可能有0>gt；WHERe设备时间戳<GETDATE((

select(
SELECT MAX(kvarh)
FROM [Transformer].[dbo].[Total_Power]
WHERe DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996')
-
(SELECT MIN(kvarh)  /*could have 0 value*/
FROM [Transformer].[dbo].[Total_Power]
WHERe DeviceTimeStamp < GETDATE()
and DEVICEIMEI = '868996'
and kvarh >0)/*try adding this*/
as Difference