MySQL从多个获取最后日期记录 [英] MySQL get last date records from multiple

问题描述

我的数据库中有以下内容:

I have the following in my db:

ID      datetime             type   value
1094    2013-09-25 09:11:18  HDD      89%
1094    2013-09-25 09:11:18  CPU      55%
1093    2013-09-25 09:11:18  HDD      77%
1093    2013-09-25 09:11:18  CPU      55%
1094    2013-09-25 08:21:28  HDD      85%
1094    2013-09-25 08:21:28  CPU      11%
1093    2013-09-25 07:04:00  HDD      76%

我想获取每个ID的最后日期并键入记录:

I want to get the last date for every ID and type record:

1094    2013-09-25 09:11:18  HDD      89%
1094    2013-09-25 09:11:18  CPU      55%
1093    2013-09-25 09:11:18  HDD      77%
1093    2013-09-25 09:11:18  CPU      55%

推荐答案

您可以使用GROUP BY，但是了解其工作原理很重要.

You can use GROUP BY, but it's important to understand how it works.

当您使用GROUP BY时，一组行将折叠成一个行，而在GROUP BY子句中未使用的其他列中显示的内容，则是通过使用聚合函数确定的，或者是随机的行输出该组的.您不能确保获取与显示的行相对应的行，该行包含MAX()值或您使用的任何聚合函数.当您执行以下查询时，这一点尤其明显:

When you use GROUP BY a group of rows is collapsed into one and what is displayed in the other columns that are not used in your GROUP BY clause, is determined either by using aggregate functions or it is a random row out of that group. You can't be sure to get the row corresponding to the row displayed holding the MAX() value or whatever aggregate function you used. This becomes especially obvious, when you do a query like:

SELECT id, MIN(whatever), MAX(whatever), another_column
FROM your_table
GROUP BY id

另一个列"可以属于保存MIN()值或MAX()值的行，甚至可以属于完全不显示的另一行.在除MySQL以外的其他RDBMS中，实际上是禁止选择不使用聚合函数或未在GROUP BY子句中列出的列，只是为了防止用户出错或认为自己得到了正确的结果.

The "another_column" can belong to the row holding the MIN() value or the MAX() value or even to another row not displayed at all. In other RDBMS than MySQL it's actually forbidden to select columns that do not use an aggregate function or are not listed in the GROUP BY clause, just to prevent that users make a mistake or think they got the right result.

因此，在您的情况下，您似乎也希望显示值"列.由于上述原因，juergen_d的答案是错误的，因为它没有选择值"，如果选择了值"，则不能确保它是正确的值". Filipe Silva的答案是错误的，因为它按值"分组.在您的情况下，这样做实际上将返回整个表. Romesh的答案是错误的，因为使用两次MAX()函数将获得最大值，显然是最大值，而不是与datetime值相对应的值.

So, in your case you seem to want to display the column "value" too. For above reasons, juergen_d's answer is wrong, because it doesn't select "value", and if it would, you can't be sure it's the right "value". Filipe Silva's answer is wrong, because it groups by "value". Doing this will in your case actually return the whole table. Romesh's answer is wrong, because using two times the MAX() function will get you the max values, obviously but not the value corresponding to the datetime value.

那么，您必须怎么做呢? 这里描述了3种方式.通过自己应用来学习它们.并不难:)

So, how do you have to do it? Here 3 ways are described. Learn them by applying them yourself. It's not that hard :)

这篇关于MySQL从多个获取最后日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！