mysql - SQL根据2个表连接获取最后插入的5个项目

Question

我有 2 个表格，与项目/图像相关:

[project]
project_id (int primary key)
completed (date)

[project_media]
project_media_id (int primary key)
project_id (int foreign key)
image (varchar)

我要选择的是:
根据已完成的最后 5 个项目记录，根据project_media_id 获取最后添加的图像。

换句话说，是从最后 5 个项目插入的最后一个图像。

到目前为止我所拥有的:

select project.completed, project_media.project_id, project_media.image 
from project 
inner join project_media 
    on project.project_id = project_media.project_id 
order by completed desc limit 5

Answer 1

SELECT PR.*, 
(
  SELECT PM.image FROM project_media PM
  WHERE PR.project_id = PM.project_id
  ORDER BY PM.project_media_id DESC
  LIMIT 1
) AS image
FROM 
(
  SELECT P.project_id, P.completed 
  FROM project P
  ORDER BY P.completed DESC
  LIMIT 5
) AS PR