我在执行此代码时遇到问题:
SELECT * FROM tblpm n
WHERE date_updated=(SELECT MAX(date_updated)
FROM tblpm GROUP BY control_number
HAVING control_number=n.control_number)
基本上,我想返回每个控制编号的最新日期.上面的查询返回正确的输出,但需要37秒.在显示输出之前.
有没有其他sql子句或命令可以比上面的查询执行得更快?
提前谢谢.
我在执行此代码时遇到问题:
SELECT * FROM tblpm n
WHERE date_updated=(SELECT MAX(date_updated)
FROM tblpm GROUP BY control_number
HAVING control_number=n.control_number)
基本上,我想返回每个控制编号的最新日期.上面的查询返回正确的输出,但需要37秒.在显示输出之前.
有没有其他sql子句或命令可以比上面的查询执行得更快?
提前谢谢.
将子查询放入WHERE子句并将其限制为n.control_number意味着它会多次运行子查询.这被称为correlated subquery,它通常是性能杀手.
最好在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);