MS SQL 查询，用于每隔 15 分钟检索一次的数据

Question

Im using MS Sql Server 2008 r2 Database .我尝试每隔 15 分钟从数据库中获取数据。所以我尝试了这个查询

我的表名是记录，我有日期、温度、压力等列

SELECT MIN([Date]) AS Rext 
FROM Records where Date between '2017-01-27 00:00:00' and '2017-02-11 00:00:00'
GROUP BY DATEPART(YEAR, [Date]),
DATEPART(MONTH, [Date]),
DATEPART(DAY, [Date]),
DATEPART(HOUR, [Date]),
(DATEPART(MINUTE, [Date]) / 15)
ORDER BY Rext

此查询对我有用，并且我正在获取具有 15 个时间间隔的日期列。这是我的输出

2017-01-27 18:45:43.000
2017-01-27 19:00:35.000
2017-01-27 19:15:35.000
2017-01-27 19:30:35.000
2017-01-27 19:45:35.000
2017-01-27 20:00:35.000
2017-01-27 20:15:35.000
2017-01-27 20:30:35.000
2017-01-27 20:45:35.000

但是当我尝试检索其他列(温度和压力(以及日期列时，我获得输出但间隔不匹配.我的查询就像

SELECT MIN([Date]) AS Rext , Temperature, Pressure
FROM Records where Date between '2017-01-27 00:00:00' and '2017-02-11 00:00:00'
GROUP BY DATEPART(YEAR, [Date]),
DATEPART(MONTH, [Date]),
DATEPART(DAY, [Date]),
DATEPART(HOUR, [Date]),
(DATEPART(MINUTE, [Date]) / 15)
ORDER BY Rext,Temperature, Pressure

我的输出是

2017-01-27 18:45:43.000 18.1        57        
2017-01-27 18:46:43.000 17.9        57        
2017-01-27 18:47:43.000 17.9        58        
2017-01-27 18:50:35.000 18          57        
2017-01-27 19:00:35.000 18          57        
2017-01-27 19:03:35.000 17.9        57        
2017-01-27 19:15:35.000 18.1        57        
2017-01-27 19:16:35.000 18          57        
2017-01-27 19:21:35.000 17.9        57        
2017-01-27 19:30:35.000 18          57        
2017-01-27 19:31:35.000 18.1        57        
2017-01-27 19:33:35.000 17.9        57        
2017-01-27 19:38:35.000 17.9        58        
2017-01-27 19:39:34.000 17.9        56        
2017-01-27 19:40:35.000 17.8        54        
2017-01-27 19:41:35.000 17.9        53        
2017-01-27 19:42:35.000 18          49        
2017-01-27 19:43:35.000 17.9        49        
2017-01-27 19:44:35.000 18          46        
2017-01-27 19:45:35.000 17.9        43        
2017-01-27 19:46:35.000 18          42        
2017-01-27 19:47:35.000 17.9        40        
2017-01-27 19:48:34.000 18          39        
2017-01-27 19:49:35.000 18.1        37        
2017-01-27 19:50:35.000 18.3        36        
2017-01-27 19:51:35.000 18.5        34        
2017-01-27 19:52:35.000 18.6        33        
2017-01-27 19:53:35.000 18.7        31        
2017-01-27 19:54:35.000 18.8        30        
2017-01-27 19:55:35.000 19.1        29        
2017-01-27 19:56:35.000 19.3        28        
2017-01-27 19:58:35.000 19.5        27        
2017-01-27 19:59:35.000 19.6        27        
2017-01-27 20:00:35.000 19.7        26        
2017-01-27 20:01:34.000 19.5        26        
2017-01-27 20:02:35.000 19.4        26        
2017-01-27 20:04:35.000 19.5        25        
2017-01-27 20:06:35.000 19.4        25        
2017-01-27 20:07:35.000 19.3        25        
2017-01-27 20:15:35.000 19.4        25        
2017-01-27 20:16:34.000 19.3        25        
2017-01-27 20:18:35.000 19.5        25        
2017-01-27 20:20:35.000 19.5        24        
2017-01-27 20:21:35.000 19.3        24        
2017-01-27 20:22:35.000 19.4        24        
2017-01-27 20:28:35.000 19.6        24        
2017-01-27 20:30:35.000 19.6        24        
2017-01-27 20:31:35.000 19.5        24        
2017-01-27 20:35:35.000 19.4        24        
2017-01-27 20:45:35.000 19.6        24        
2017-01-27 20:47:35.000 19.5        24        
2017-01-27 21:00:35.000 19.5        24        
2017-01-27 21:01:35.000 19.6        23        
2017-01-27 21:05:47.000 19.7        23        
2017-01-27 21:14:47.000 19.8        23        
2017-01-27 21:15:47.000 19.7        23        
2017-01-27 21:16:47.000 19.8        23        
2017-01-27 21:20:47.000 19.8        22  

请任何人帮助我. 谢谢

Answer 1

SELECT DATEADD(minute,DATEDIFF(minute,0,Date)/15*15,0) AS Rext , MIN(Temperature), MAX(Pressure)
FROM Records where Date between '2017-01-27 00:00:00' and '2017-02-11 00:00:00'
GROUP BY DATEADD(minute,DATEDIFF(minute,0,Date)/15*15,0), Temperature, Pressure
ORDER BY 1

快捷方式：每当使用聚合条件时，选择中的所有其他列(非聚合(都应位于分组依据子句中