mysql输出表中记录_使用mysql从表中获取最新记录

这一定很简单,但我被难住了。我习惯在MsAccess中使用聚合“FIRST”,但MySql没有这样的功能。

这是一张简单的桌子。我想根据日期返回最近的记录,

我被要求添加我的完整查询。我曾尝试使用联接功能将“t”替换为临时表名,但失败。“无法重新打开表“t”

代码如下。我知道它很难看,但它确实返回了正确的数据集。

enter code here

DROP TABLE IF EXISTS `tmpMaxLookupResults`;

创建临时表tmpMaxLookupResults

作为

SELECT

REPORTS.dtmReportCompleted,

RESULTS.lngMainReport_ID, RESULTS.lngLocationGroupSub_ID

FROM

(tbl_010_040_ProcedureVsTest_Sub as ProcVsSub

INNER JOIN tbl_010_050_CheckLog_RESULTS as RESULTS

ON (ProcVsSub.lngLocationGroupSub_ID = RESULTS.lngLocationGroupSub_ID)

AND (ProcVsSub.lngProcedure_ID = RESULTS.lngProcedure_ID)

AND (ProcVsSub.lngItemizedTestList_ID = RESULTS.lngItemizedTestList_ID)

AND (ProcVsSub.strPasscodeAdmin = RESULTS.strPasscodeAdmin)

AND (ProcVsSub.strCFICode = RESULTS.strCFICode))

INNER JOIN

tbl_000_010_MAIN_REPORT_INFO as REPORTS ON (RESULTS.lngPCC_ID =

REPORTS.lngPCC_ID)

AND (RESULTS.lngProcedure_ID = REPORTS.lngProcedure_ID)

AND (RESULTS.lngMainReport_ID = REPORTS.idMainReport_ID)

AND (RESULTS.strPasscodeAdmin = REPORTS.strPasscodeAdmin)

AND (RESULTS.strCFICode = REPORTS.strCFICode)

WHERE

(((RESULTS.lngProcedure_ID) = 143)

AND ((RESULTS.dtmExpireDate) IS NOT NULL)

AND ((RESULTS.strCFICode) = 'ems'))

GROUP BY RESULTS.lngMainReport_ID, RESULTS.lngLocationGroupSub_ID

ORDER BY (REPORTS.dtmReportCompleted) DESC;

SELECT t.*

FROM tmpMaxLookupResults AS t

JOIN (

SELECT lngLocationGroupSub_ID,

MAX(dtmReportCompleted) AS max_date_completed

FROM tmpMaxLookupResults

GROUP BY lngLocationGroupSub_ID ) AS dt

ON dt.lngLocationGroupSub_ID = t.lngLocationGroupSub_ID AND

dt.max_date_completed = t.dtmReportCompleted

enter code here