php - 加入两个MySQL查询，其中条件和最新时间​​戳记在何处

第一个查询是针对选定的机构，第二个查询是对该机构的最新审查。

第一个查询：

SELECT
    instituteId,
    instituteName,
    description
FROM institutions
WHERE instituteId IN ('1','2','3')";

第二个查询：

SELECT
    name,
    review,
    timestamp
FROM reviews
WHERE instituteId='1'
ORDER BY timestamp DESC
LIMIT 1;

最佳答案

SELECT i.instituteId,
       i.instituteName,
       i.description ,
       r.name,
       r.review,
       r.timestamp
FROM institutions i
INNER JOIN review r
ON i.instituteid = r.instituteId
INNER JOIN (SELECT instituteId,
                   MAX(timestamp) as timestamp
            FROM reviews
            GROUP BY instituteId
            ) r1
ON r.instituteid = r1.instituteId
AND r.timestamp = r1.timestamp
WHERE instituteId IN ('1','2','3')