MySQL 使用 MAX() 和 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9966345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL using MAX() with WHERE clauses
提问by Joshua Bambrick
I am having some issues creating a mySQL query with PHP. We have one table, called data_instant, with a series of cumulative observations being entered, called Count_Qty, and we want to extract the previous one to deduct from the new observation to calculate the increase.
我在用 PHP 创建 mySQL 查询时遇到了一些问题。我们有一个名为 data_instant 的表，其中输入了一系列累积观测值，称为 Count_Qty，我们希望提取前一个以从新观测值中减去以计算增加量。
$result = mysql_query("SELECT *,
MAX(Record_Time)
FROM data_instant
WHERE Node_ID='$nodeID' AND Type='$type';
$row = mysql_fetch_array ($result);
Basically I'd expect the max Record_Time row to be returned, but instead it's just the first instance that's received. The previous observation has the highest Record_Time (a Unix datestamp), so it's unclear to me why this would not work...
基本上我希望返回最大 Record_Time 行，但它只是收到的第一个实例。先前的观察具有最高的 Record_Time（Unix 日期戳），所以我不清楚为什么这不起作用......
回答by Andriy M
If you want to get the row with the latest Record_Timevalue, just sort the rows in the descending order of Record_Timeand get the top row:
如果要获取具有最新Record_Time值的行，只需按 的降序对行进行排序Record_Time并获取顶行：
SELECT *
FROM data_instant
WHERE Node_ID='$nodeID'
AND Type='$type'
ORDER BY Record_Time DESC
LIMIT 1;
回答by Klas Lindb?ck
The whereclause selects all rows matching Node_ID='$nodeID' AND Type='$type'.
该where子句选择与 Node_ID='$nodeID' AND Type='$type' 匹配的所有行。
For each of those rows it will return all fields and the maximum record time.
对于这些行中的每一行，它将返回所有字段和最大记录时间。
If you want the row with the maximum record time you need to add that to your whereclause:
如果您想要具有最长记录时间的行，则需要将其添加到您的where子句中：
SELECT *
FROM data_instant
WHERE Node_ID='$nodeID' AND Type='$type'
and Record_Time = (select MAX(Record_Time)
FROM data_instant
WHERE Node_ID='$nodeID'
AND Type='$type')
回答by GavinCattell
If you have more than the MAX being returned you should add the GROUP BYto your statement to ensure the correct aggregation:
如果您返回的数量超过 MAX，您应该将GROUP BY加到您的语句中以确保正确聚合：
SELECT columnA,
columnB,
MAX(Record_Time)
FROM data_instant
WHERE Node_ID='$nodeID' AND Type='$type'
GROUP BY columnA, columnB;
回答by Mosty Mostacho
I'd expect the max Record_Time row to be returned
我希望返回最大 Record_Time 行
Then you should just ask for that column:
那么你应该只要求该列：
SELECT MAX(Record_Time) FROM data_instant
WHERE Node_ID='$nodeID' AND Type='$type'
This will return the max record_time for the specified node_id and type.
这将返回指定 node_id 和类型的最大记录时间。
