在SQL Server中查找最大值的时间
我有一个表 Table1，其中包含字段 TimeStamp 和 Humidity，其值是：
TimeStamp
'2010-09-29 11:05:29.6'
'2010-09-29 11:05:29.7'
'2010-09-29 11:05:29.8'
'2010-09-29 11:05:29.9'
'2010-09-29 11:05:30.0'
湿度
15.291
17.379
16.857
16.335
15.813
我想运行一个查询，返回湿度达到最大值时的时间戳值。在此示例中，它将返回“2010-09-29 11:05:29.7”，因为此时湿度为其最高值 17.379。我还想限制时间范围，所以它会是这样的
SELECT _TimeStamp from Table1
WHERE Humidity = MAX(Humidity) AND
_TimeStamp >= '2010-09-29 11:05:29.6' AND
_TimeStamp <= '2010-09-29 11:05:30.0'
，但这会给出一个错误，即在 where 子句中不允许聚合。这个查询应该如何正确编写？
I have a table, Table1, containing the fields TimeStamp and Humidity, which have the values:
TimeStamp
'2010-09-29 11:05:29.6'
'2010-09-29 11:05:29.7'
'2010-09-29 11:05:29.8'
'2010-09-29 11:05:29.9'
'2010-09-29 11:05:30.0'
Humidity
15.291
17.379
16.857
16.335
15.813
I would like to run a query that returns the value of TimeStamp at the instant that Humidity is at its maximum. In this example it would return '2010-09-29 11:05:29.7' because that is when Humidity is its highest value, 17.379. I also want to limit the time range, so it would be something like
SELECT _TimeStamp from Table1
WHERE Humidity = MAX(Humidity) AND
_TimeStamp >= '2010-09-29 11:05:29.6' AND
_TimeStamp <= '2010-09-29 11:05:30.0'
but this gives an error that aggregates are not permitted in a where clause. How should this query be written correctly?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
或者，如果您想返回与最大湿度匹配的所有时间戳，请
SELECT TOP 1 WITH TIES _TimeStamp。对于更复杂的分组场景，您应该研究排名函数，例如
row_numberOr
SELECT TOP 1 WITH TIES _TimeStampif you want to bring back all timestamps matching the max humidity.For more complicated grouping scenarios you should investigate the ranking functions such as
row_number