Mysql-聚合函数查询分组问题

发布于 2024-11-29 03:36:40 字数 652 浏览 1 评论 0原文

考虑下表。


在此处输入图像描述


我正在尝试编写一个查询来显示 - 每个类别的所有零件的最大值。还显示该值达到最大值的日期。

所以我尝试了这个 -

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.


enter image description here


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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

猥琐帝 2024-12-06 03:36:40

您需要连接到查找 max(value) 的查询结果,如下所示:

select dt.Part_id, dt.Category, dt.Value, dt.Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature';

请注意,如果有多行具有相同的最大值,这将返回多行。

如果您想将其限制为一行,即使 max(value) 有多个匹配项,请选择 max(Time_Captured)(如果您愿意,也可以选择 min(Time_Captured)),如下所示:

select dt.Part_id, dt.Category, dt.Value, max(dt.Time_Captured) as Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature'
group by 1, 2, 3;

You need to join to the results of a query that finds the max(value), like this:

select dt.Part_id, dt.Category, dt.Value, dt.Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature';

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:

select dt.Part_id, dt.Category, dt.Value, max(dt.Time_Captured) as Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature'
group by 1, 2, 3;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文