按日期和主要连接查询 [英] Join query by date and primary

问题描述

我的数据库中有以下表格

I have the followings tables on my database

有几个项目状态(等待、工作、完成、已付款)，一个项目必须经历从等待到付款的所有状态.

There are several project statuses (waiting, working, finished, paid) and a project have to pass through all the status from waiting to paid.

为了保持对项目的完整跟踪，每个状态都被保留，所以我们知道一个项目何时改变了它的状态.

In order to keep complete track of the project, each status are kept, so we know when a project changed its status.

我想创建一个具有当前状态的搜索过滤器(带有最近的created_at").

I would like to create a search filter with the current status (the one with the more recent "created_at").

为了得到正确的结果，我尝试了几次查询，但我坚持使用这个.

I tried few queries in order to get the correct result, but I'm stuck with this one.

我的无效查询是

select p.* from projects p
left join projects_status_projects psp on psp.project_id = p.id
where p.id in (select project_id from projects_status_projects 
      where project_status_id = XX group by project_id order by created_at desc)

Ofc，此查询不起作用，并在不考虑顺序的情况下向我返回每个项目，并记录为 project_status_id XX.

Ofc, this query does not work and return me each projects with a record for project_status_id XX without taking in account the order.

任何提示将不胜感激！

谢谢，
本杰明

推荐答案

select p.* from projects p
left join projects_status_projects psp on (psp.project_id = p.id)
where created_at = (select max(created_at) 
                    from projects_status_projects where
                    project_id = p.id)        

sqlfiddle 这里有一些例子:http://sqlfiddle.com/#!2/725fcc/7/0

sqlfiddle with some example here: http://sqlfiddle.com/#!2/725fcc/7/0

这篇关于按日期和主要连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！