Mysql-聚合函数查询分组问题
考虑下表。
我正在尝试编写一个查询来显示 - 每个类别的所有零件的最大值。还显示该值达到最大值的日期。
所以我尝试了这个 -
select Part_id, Category, max(Value), Time_Captured
from data_table
where category = 'Temperature'
group by Part_id, Category
首先,mysql没有因为group by中没有Time_Captured而抛出错误。 不确定是 mysql 还是 my mysql 的问题。
所以我认为它应该返回 -
1 Temperature 50 11-08-2011 08:00
2 Temperature 70 11-08-2011 09:00
但它返回我从数据集的第一条记录捕获的时间,即 11-08-2011 07:00
不确定我哪里出错了。有什么想法吗?
(注意:我在虚拟机内运行它。以防万一它会改变任何内容)
Consider following table.
I'm trying to write a query to display - Max values for all the parts per category. Also display the date when the value was max.
So i tried this -
select Part_id, Category, max(Value), Time_Captured
from data_table
where category = 'Temperature'
group by Part_id, Category
First of all, mysql didn't throw an error for not having Time_Captured in group by.
Not sure if its a problem with mysql or my mysql.
So I assume it should return -
1 Temperature 50 11-08-2011 08:00
2 Temperature 70 11-08-2011 09:00
But its returning me the time captured from the first record of the data set i.e. 11-08-2011 07:00
Not sure where I'm going wrong. Any thoughts?
(Note: I'm running this inside a VM. Just in case if it changes anything)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您需要连接到查找 max(value) 的查询结果,如下所示:
请注意,如果有多行具有相同的最大值,这将返回多行。
如果您想将其限制为一行,即使 max(value) 有多个匹配项,请选择 max(Time_Captured)(如果您愿意,也可以选择 min(Time_Captured)),如下所示:
You need to join to the results of a query that finds the max(value), like this:
Note that this will return multiple rows if there are multiple rows with the same max value.
If you want to limit this to one row even though there are multiple matches for max(value), select the max(Time_Captured) (or min(Time_Captured) if you prefer), like this: