mysql - 按日期分组并取最后一个

Question

这是我的 table :

我想做的是，由 caserne 承担用户的最后责任。例如，我应该得到这个结果:

id    id_user    id_caserne    id_dispo    created_at
31    21         12            1           2019-10-24 01:21:46
33    21         13            1           2019-10-23 20:17:21

我已经尝试过这个sql，但它似乎并不总是有效:

SELECT * FROM 
    ( SELECT id, id_dispo, id_user, id_caserne, MAX(created_at) 
    FROM disponibilites GROUP BY id_user, id_caserne, id_dispo 
    ORDER BY created_at desc ) AS sub 
GROUP BY id_user, id_caserne

我做错了什么？

Answer 1

我只需在 where 子句中使用相关子查询进行过滤即可:

select d.*
from disponibilites d
where d.created_at = (select max(d2.created_at)
                      from disponibilites d2
                      where d2.id_user = d.id_user
                     );

编辑:

根据您的评论:

select d.*
from disponibilites d
where d.created_at = (select max(d2.created_at)
                      from disponibilites d2
                      where d2.id_user = d.id_user and
                            d2.id_caserne = d.id_caserne
                      where date(d2.created_at) = date(d.created_at)
                     );