我需要写一个查询，我认为将需要一个子查询在它。 目前，我正在使用DataGrip将查询作为原始SQL语句编写，并且需要在postGres服务器上工作。 我正在使用Laravel编写这个查询需要在其中工作的应用程序。
编写查询所需的两个表是media_files和statuses。 这两个表之间有一个链接:media_files.id=statuses.model_id
文件存储在media_files中，可以有两种状态:挂起和附加。 文件的状态存储在statuses中。 statuses表还可以包含任务，事件，用户等其他事物的状态。
我需要一种方法得到所有文件的最后状态为他们是挂起。 有些文件甚至可能没有挂起状态，可以忽略这些文件。
状态表可以保存同一媒体文件的多个状态。 例如，您可以使用:
记录1
media_files.id=1
media_files.name=“CV文档”
statuses.id=2
statuses.model_id=1
statuses.model_type=“app\mediaFile”
statuses.name=“附加”
statuses.created_at='2020-06-16 17:39:08'
记录2
media_files.id=1
media_files.name=“CV文档”
statuses.id=1
statuses.model_id=1
statuses.model_type=“app\mediaFile”
statuses.name='pending'
statuses.created_at='2020-06-14 17:30:00'
我已经开始查询了，但它似乎不能正常工作:
select media_files.*, (
select name
from statuses
where model_id = media_files.id
and model_type = 'App\File'
order by statuses.created_at desc
limit 1
)
as latest_status
from media_files
inner join statuses on statuses.model_id = media_files.id
where statuses.model_type = 'App\Entities\Media\File'
order by media_files.id desc;
您可以使用条件聚合来确定最后挂起的状态是否是最后一个状态:
select mf.*
from media_files mf join
(select s.model_id,
max(case when s.status = 'pending' then s.created_at end) as last_pending_created_at,
max(s.created_at) as last_created_at
from statuses s
group by s.model_id
) s
on s.model_id = mf.id
where last_pending_created_at = last_created_at;
还可以使用相关子查询:
select mf.*
from (select mf.*,
(select s.status
from statuses s
where s.model_id = mf.id
order by s.created_at desc
limit 1
) as last_status
from media_files mf
) mf
where last_status = 'pending';
您也可以使用解析函数
SELECT MEDIA_FILES.*,LATEST_STATUS.*
FROM
(SELECT NAME,MODEL_ID,
MAX(CREATED_AT) OVER(PARTITION BY NAME) AS MAX_TM
WHERE MODEL_ID = MEDIA_FILES.ID
AND MODEL_TYPE = 'App\File') AS LATEST_STATUS,MEDIA_FILES
WHERE LATEST_STATUS.MODEL_ID=MEDIA_FILES.MODEL_ID
