sql—在接下来的几个小时内从两个日期差中查找列的最小值

qxgroojn  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(414)

找到从未来72小时到日期“2020-07-06t00:00:00.000z”的最低温度值的可能方法?

SELECT [WeatherForcastDetails].[StartTime],
[WeatherForcastDetails].[Temperature],
[WeatherForcastDetails].[Id] 
FROM [WeatherForcastDetails]WHERE ([WeatherForcastDetails].[StartTime] > N'2020-07-06T00:00:00.000Z' 
AND [WeatherForcastDetails].[StartTime] < N'2020-07-10T00:00:00.000Z')
AND [WeatherForcastDetails].[WeatherForcastId] in (6146)


另外如果我有更多的天气预报

SELECT wfd.StartTime,wfd.Temperature,wfd.WeatherForcastId
FROM WeatherForcastDetails as wfd 
WHERE (wfd.StartTime> N'2020-0706T00:00:00.000Z' 
AND wfd.StartTime < N'2020-07-10T00:00:00.000Z')
AND wfd.WeatherForcastId in (6146,6163,6180)


我需要从查询中给定的日期范围中找到未来72小时内温度最低的每天数据(检查三天的最低温度)。根据weatherforcastid,weatherforcastid可以重复。
暗示

select case when val1 < val2 then
           case when val1 < val3 then val1
           else val3
           end
    when val2 < val3 then val2
           else val3
end

从cols;
或者类似的

SELECT 
MIN([WeatherForcastDetails].[Temperature]),
MIN([WeatherForcastDetails].[WeatherForcastId]),
(
SELECT MIN(wfd.Temperature)
FROM WeatherForcastDetails as wfd WHERE (wfd.StartTime> N'2020-07-06T00:00:00.000Z' 
AND wfd.StartTime < DATEADD(hour, 72,  N'2020-07-06T00:00:00.000Z'))) as minTpr
FROM [WeatherForcastDetails]
WHERE ([WeatherForcastDetails].[StartTime] > N'2020-07-06T00:00:00.000Z'
AND [WeatherForcastDetails].[StartTime] < N'2020-07-10T00:00:00.000Z')
AND [WeatherForcastDetails].[WeatherForcastId] in
(6146,6163,6180,6198,6238,6244,6250)
GROUP BY [WeatherForcastDetails].[StartTime]
pkmbmrz7

pkmbmrz71#

我想你想要:

SELECT TOP (1) wfd.*
FROM WeatherForcastDetails wfd
WHERE wfd.StartTime >= '2020-07-06' AND
      wfd.StartTime < DATEADD(hour, 72, '2020-07-06')
      wfd.WeatherForcastId in (6146)
ORDER BY wfd.Temperature ASC;

笔记:
表别名使查询更易于编写和读取。
方括号使查询更难写和读,通常不需要。
日期常量不需要显式的时间组件。你可以把它包括进去,但似乎没有必要。
我建议你避免 DATEDIFF() ,因为它没有达到预期的效果。它计算两个日期/时间值之间的“界限”,而不是实际的差异。
编辑:
如果要对每个天气预报id执行此操作,请使用窗口函数:

SELECT wfd.*
FROM (SELECT wfd.*,
             ROW_NUMBER() OVER (PARTITION BY wfd.WeatherForcastId ORDER BY wfd.Temperature ASC) as seqnum
      FROM WeatherForcastDetails wfd
      WHERE wfd.StartTime >= '2020-07-06' AND
            wfd.StartTime < DATEADD(hour, 72, '2020-07-06')
            wfd.WeatherForcastId in (6146)
     ) wfd
WHERE seqnum = 1;
3htmauhk

3htmauhk2#

如果我没听错,有一个选项是过滤,对结果进行排序并只保留第一行:

select top(1) w.*
from WeatherForcastDetails w
where 
    WeatherForcastId = 6146
    and StartTime >= '20200706'
    and StartTime <  '20200710'
order by Temperature

相关问题