MySQL GROUP BY 有 MAX 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18221999/
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
GROUP BY having MAX date
提问by J-J
I have problem when executing this code:
执行此代码时遇到问题：
SELECT * FROM tblpm n
WHERE date_updated=(SELECT MAX(date_updated)
FROM tblpm GROUP BY control_number
HAVING control_number=n.control_number)
Basically, I want to return the most recent date for each control number. The query above returns correct output but it takes 37secs. before the output was shown.
基本上，我想返回每个控制号的最近日期。上面的查询返回正确的输出，但需要 37 秒。在显示输出之前。
Is there any other sql clause or command that can execute faster than the query above?
是否有任何其他 sql 子句或命令可以比上面的查询执行得更快？
Thanks in advance.
提前致谢。
回答by Bill Karwin
Putting the subquery in the WHERE clause and restricting it to n.control_number means it runs the subquery many times. This is called a correlated subquery, and it's often a performance killer.
将子查询放在 WHERE 子句中并将其限制为 n.control_number 意味着它会多次运行子查询。这称为相关子查询，它通常是性能杀手。
It's better to run the subquery once, in the FROM clause, to get the max date per control number.
最好在 FROM 子句中运行一次子查询，以获取每个控制号的最大日期。
SELECT n.*
FROM tblpm n
INNER JOIN (
SELECT control_number, MAX(date_updated) AS date_updated
FROM tblpm GROUP BY control_number
) AS max USING (control_number, date_updated);
回答by Micah Hahn
There's no need to group in that subquery... a where clause would suffice:
没有必要在该子查询中分组……一个 where 子句就足够了：
SELECT * FROM tblpm n
WHERE date_updated=(SELECT MAX(date_updated)
FROM tblpm WHERE control_number=n.control_number)
Also, do you have an index on the 'date_updated' column? That would certainly help.
另外，您在“date_updated”列上有索引吗？那肯定会有帮助。
回答by Claudio Shigueo Watanabe
Another way that doesn't use group by:
另一种不使用 group by 的方法：
SELECT * FROM tblpm n
WHERE date_updated=(SELECT date_updated FROM tblpm n
ORDER BY date_updated desc LIMIT 1)
回答by jaiwithani
Fast and easy with HAVING:
使用 HAVING 快速简便：
SELECT * FROM tblpm n
FROM tblpm GROUP BY control_number
HAVING date_updated=MAX(date_updated);
In the context of HAVING, MAXfinds the max of each group. Only the latest entry in each group will satisfy date_updated=max(date_updated). If there's a tie for latest within a group, both will pass the HAVINGfilter, but GROUP BYmeans that only one will appear in the returned table.
在 的上下文中HAVING，MAX找到每个组的最大值。只有每个组中的最新条目才能满足date_updated=max(date_updated)。如果组中有最新的并列，则两者都将通过HAVING过滤器，但这GROUP BY意味着只有一个将出现在返回的表中。
