Mysql按一个字段分组，按另一个字段排序

Question

也许这个问题是重复的，但我找不到适合我的任务的答案。我有下一张包含以下数据的表格:

我需要选择具有唯一 oid 和每个 oid 的最大日期的数据。我有这些查询，但它们无法正常工作:

SELECT `oid`, `pvalue`, `date`
FROM `report`
WHERE `deviceid` = 'MRA-1011'
  AND `date` <= '2012-01-20 00:00:00'
GROUP BY oid
HAVING `date` = MAX(`date`)
ORDER BY `date` DESC;

SELECT `oid`, `pvalue`
FROM `report`
WHERE `deviceid` = 'MRA-1011'
  AND `date` <= '2012-01-20 00:00:00'
GROUP BY oid
ORDER BY `date` DESC;

我想得到这个结果:

Answer 1

您需要按照以下方式使用子选择:

SELECT oid, pvalue FROM report
JOIN (SELECT oid, MAX(date) AS maxDate FROM report r WHERE `deviceid` = 'MRA-1011'
AND `date` <= '2012-01-20 00:00:00' GROUP BY oid) AS foo
ON foo.oid=r.oid AND foo.maxDate = report.date;

如果 report.date 不是唯一的，您可能希望在外部查询中按它分组