mysql - 具有附加约束的棘手 GROUP BY SQL

Question

我有一个像这样的“成员”表:

id   status    user_id   created_at
-----------------------------------
1    active    1         2015-07-01
2    active    2         2015-07-01
3    inactive  1         2015-07-04
4    deleted   2         2015-07-04
5    active    3         2015-07-04
6    active    1         2015-07-08
7    inactive  3         2015-07-08

如何查询任意给定日期所有用户的最新状态？

对于2015-07-01，输出应为:

id   status    user_id   created_at
-----------------------------------
1    active    1         2015-07-01
2    active    2         2015-07-01

对于2015-07-05，输出应为:

id   status    user_id   created_at
-----------------------------------
3    inactive  1         2015-07-04
4    deleted   2         2015-07-04
5    active    3         2015-07-04

对于2015-07-10，输出应为:

id   status    user_id   created_at
-----------------------------------
3    active    1         2015-07-08
4    deleted   2         2015-07-04
5    inactive  3         2015-07-08

非常感谢任何帮助!

Answer 1

使用 group by 和 where 来获取给定日期之前的最新日期，然后进行联接:

select m.*
from members m join
     (select user_id, max(created_at) as maxca
      from members
      where created_at <= '2015-07-08'  -- or whatever
      group by user_id
     ) mu
     on m.user_id = mu.user_id and m.created_at = mu.maxca;