Mysql: Group by , order , max 的组合。获取分组集的最大值并应用条件

Question

mysql数据库中有一张reservations表，格式如下。

id | customer_id | user_id | date_booked
1  | 1           | 1       | 2012-11-5

我想获取特定用户的客户数。所以我写了查询

select count(*), user_id, customer_id,date_booked from reservations where user_id=1 group by customer_id

这会以所需的方式获取结果。但是 date_booked ，我得到了记录的第一个值。即，如果 id 为 2 的客户有 4 条记录，它会获取第一条记录的 date_booked 值。我想要最新的值，所以我改进了如下查询

select count(*), user_id, customer_id,max(date_booked) from reservations where user_id=1 group by customer_id

这会获取记录的最高日期。这仍然不是我要获取的结果集。我想要最新的日期，但应该小于当前日期(今天)。

有没有办法这样写条件

max(date_booked)<date(now())

我想获取最大日期但小于今天。我想在 CakePHP 中实现这一点。如果有人可以帮助查询，我可以将其写成 cakePHP 格式。

如有必要，请改进问题。

Answer 1

select  count(*),a.user_id,a.customer_id,
   IF(max(a.date_booked)>now(), b.date_booked, max(a.date_booked)) as date_booked
from reservations a left join 
   (select max(date_booked) as date_booked,customer_id 
        from reservations where date_booked < now() group by customer_id) b
on a.customer_id=b.customer_id
where a.user_id=1 
group by customer_id