MySQL:从组中选择最后一个，但有条件

Question

我有下表:

name     created_for created_on            status
jen      2012-05-27  2012-02-15 12:14:09    Y
joe      2012-10-01  2012-02-15 12:22:09    Y
bob      2012-10-01  2012-02-15 12:47:09    N
jim      2012-10-01  2012-02-24 12:47:09    N
jim      2012-10-01  2012-02-29 12:47:09    Y
sam      2012-10-01  2012-03-11 12:47:09    N
sam      2012-10-01  2012-03-15 12:47:09    C
sam      2012-10-01  2012-03-22 12:47:09    Y
tom      2012-10-01  2012-04-10 12:47:09    N
tom      2012-10-01  2012-04-11 12:47:09    N

我想要返回的是姓名的最后一行，created_on 对于每个创建日期为 2012-10-01 的人，但前提是最后一个条目的状态是 N。所以对于上表，我想回来:

name     created_on
bob      2012-02-15 12:47:09
tom      2012-04-11 12:47:09

状态条件让我感到困惑。我似乎无法弄清楚将“where status='N'”放在哪里才能获得正确的结果。

Answer 1

试试这个:

 SELECT t1.*
 FROM
 (
    SELECT name, MAX(created_on) MaxDate
    FROM YourTableName
    GROUP BY name
  ) t1 INNER JOIN YourTableName t2  on t1.name    = t2.name 
                                   AND t1.MaxDate = t2.created_on
 WHERE t2.created_for = '2012-10-01'
   AND t2.status = 'N'