MySql根据另一个表中的MAX值选择一个表中的所有行 [英] MySql select all rows in one table based on MAX value in another table

问题描述

我希望能够从下面的表1和表3中获取所有数据，但除此之外，我还希望从表2中获取最新的应用程序阶段.最新的应用程序阶段是通过获取以下文件的最大stage_date来确定的:每个应用程序.

I want to be able to get all the data from table 1 and table 3 below but in addition to this I also want to get the latest application stage from table 2. The latest application stage is determined by getting the max stage_date for each application.

表1:应用程序

id | applicant_id | col_x | col_y | col_z
-----------------------------------------
10   300            a       b       c    
11   310            a       b       c    
12   320            a       b       c    
13   330            a       b       c    
14   340            a       b       c    

表2:application_progress

id | application_id | application_stage | stage_date | stage_notes
------------------------------------------------------------------
1    10               DRAFT               2013-01-01   (NULL)   
2    10               APPLICATION         2013-01-14   (NULL)   
3    10               PHASE1              2013-01-30   (NULL)
4    11               DRAFT               2013-01-01   (NULL)  
4    12               DRAFT               2013-01-01   (NULL) 
5    13               DRAFT               2013-01-01   (NULL)  
6    14               DRAFT               2013-01-01   (NULL)   
7    14               APPLICATION         2013-01-14   (NULL)  

第三张表

表3:申请人

id  | applicant_name | applicant_address | programme_id 
------------------------------------------------------
300   Applicant 1      abc                 1    
310   Applicant 2      xyz                 2    
320   Applicant 3      xyz                 2    
330   Applicant 4      xyz                 2    
340   Applicant 5      xyz                 2    

返回的数据集

applicant_id | applicant_name | current_stage
---------------------------------------------------------
300            Applicant 1      PHASE1
310            Applicant 2      DRAFT
320            Applicant 3      DRAFT 
330            Applicant 4      DRAFT   
340            Applicant 5      APPLICATION

我正在努力解决这一问题，将不胜感激.

Am struggling with this one and would appreciate any help.

PS.试图举一个sqlfiddle的例子，但它在一分钟之内就失败了.如果之前没有答案，我将在备份sqlfiddle时对此进行更新.

PS. Tried to put an example of sqlfiddle but it's down at the minute. I'll update this with the sqlfiddle when it's back up if haven't had an answer before this.

推荐答案

您可以使用相关的子查询来做到这一点:

You can do this with a correlated subquery:

select a.*,
       (select application_stage
        from application_progress ap
        where ap.application_id = a.id
        order by stage_date desc
        limit 1
       ) MostRecentStage
from applications a;

您可以通过以下方式加入申请人数据:

You can joining in the applicant data with something like this::

select a.*, aa.*,
       (select application_stage
        from application_progress ap
        where ap.application_id = a.id
        order by stage_date desc
        limit 1
       ) MostRecentStage
from applications a join
     applicant aa
     on a.applicant_id = aa.id;

这篇关于MySql根据另一个表中的MAX值选择一个表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！