mysql - 您能否告诉我，我用来检索客户上次下订单时间的信息的查询出了什么问题？

Question

我正在尝试在 MySQL 工作台上编写一个查询，该查询显示客户上次从我客户的网站订购的时间。为此，我使用了两个表。一个称为“订单”，每当下达新订单时，它就会根据订单时间进行更新，并为每个新订单分配一个唯一的 ID。另一个表称为“orders_customers_details”，每当下新订单时也会更新该表，并包含下订单的客户的唯一 ID(基于客户的电子邮件地址和电话号码的组合)以及一个 key 对应订单表的id。

我的问题是我使用的查询没有返回每个客户的最新订单。对于某些客户，我会得到他们第四或第五个最近订单的日期。

我要在orders_customer_details上加入订单:

left join orders o
on ocd.id = o.customer_details_id

我还尝试使用左外连接和以下连接:

from orders o, orders_customers_details ocd
where o.customer_details_id = ocd.id

为了仅检索最近的订单，我按 customer_id HAVING max(order.id) 进行分组。仅供引用，order.id 随着每个新订单的下达而增加，因此 id 最高的订单是最近的订单。

我也尝试过

 SELECT customer_id, MAX(order.id) 

然后按customer_id分组，但无济于事。

这是完整的代码:

select customer_id, o.id as id_of_last_order, date(order_date) as 
last_ordered, timestampdiff(day, order_date, now()) as 
days_since_last_ordered
from orders o, orders_customers_details ocd
where o.customer_details_id = ocd.id
group by customer_id having max(o.id)
order by customer_id;

Answer 1

MySQL 中的典型方法是使用相关子查询来获取最近的订单:

select ocd.customer_id, o.id as id_of_last_order, 
       date(o.order_date) as last_ordered,
       timestampdiff(day, o.order_date, now()) as days_since_last_ordered
from orders o join
     orders_customers_details ocd
     on o.customer_details_id = ocd.id
where o.order_date = (select max(o2.order_date)
                      from orders o2 join
                           orders_customers_details ocd2
                           on o2.customer_details_id = ocd2.id
                      where oc2.customer_id = ocd.customer_id
                     )
order by ocd.customer_id;

当然，如果o.id是自动递增列，则最大值来自最近的日期。如果是这种情况，那么您可以使用聚合:

select ocd.customer_id,
       max(o.id) as id_of_last_order, 
       date(max(o.order_date)) as last_ordered,
       timestampdiff(day, max(o.order_date), now()) as days_since_last_ordered
from orders o join
     orders_customers_details ocd
     on o.customer_details_id = ocd.id
group by ocd.customer_id
order by ocd.customer_id;

在 MySQL 8+ 中，您只需使用窗口函数:

select *
from (select ocd.customer_id, o.id as id_of_last_order, 
             date(o.order_date) as last_ordered,
             timestampdiff(day, o.order_date, now()) as days_since_last_ordered,
             row_number() over (partition by ocd.customer_id order by o.order_date desc) as seqnum
      from orders o join
           orders_customers_details ocd
           on o.customer_details_id = ocd.id
     ) ocd
where seqnum = 1
order by ocd.customer_id;