我正在尝试在 MySQL 工作台上编写一个查询，该查询显示客户上次从我客户的网站订购的时间。为此，我使用了两个表。一种称为“订单”，每当下新订单时都会更新订单时间，并为每个新订单分配一个唯一的 ID。另一个表称为“orders_customers_details”，它也会在下新订单时更新，并包含下订单的客户的唯一 ID（基于客户的电子邮件地址和电话号码的组合），以及一个键对应于订单表的 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;
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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句