问题:需要取项目最后一次修改的内容记录
方法一:先对表进行倒序,再取LIMIT 999999999,之后再对该结果进行分组。取的结果是每组的最大值那条记录(执行效率比方法二差)
SELECT * from (
SELECT * from a ORDER BY CREATE_TIME DESC LIMIT 999999999
) b GROUP BY b.ID
方法二:先对表进行分组取最大值,再对原表记录和最大值结果取交集
select * from a
INNER JOIN (
select ID,max(CREATE_TIME) CREATE_TIME from b
) b on b.ID = a.ID and b.CREATE_TIME = a.CREATE_TIME
或
select * from a
where
(ID,CREATE_TIME) in (
select ID,max(CREATE_TIME) CREATE_TIME from b
)