按ID分组，其最大值为(date

本文介绍了按ID分组，其最大值为(date_field)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

要生成报告，我必须选择一些有关所有客户的最近交易状态的信息.到现在为止，这就是我得到的:

To build a report, I must select some information on the last transaction status of all my customers. Until now, this is what I got:

SELECT c.firstname, c.lastname, d.product_name, o.payment, s.name, h.date_add FROM ps_orders o INNER JOIN ps_order_detail d ON d.id_order = o.id_order INNER JOIN ps_customer c ON c.id_customer = o.id_customer INNER JOIN ps_order_history h ON o.id_order = h.id_order INNER JOIN ps_order_state_lang s ON s.id_order_state = h.id_order_state WHERE s.id_lang =6 GROUP BY c.id_customer HAVING MAX(h.date_add)

对于每个客户，此查询将在我需要最后一个日期时选择第一个日期(字段 h.date_add ).看来MySQL忽略了 HAVING .

For each customer, this query is selecting the first date (the field h.date_add) when I need of the last one. It seems the MySQL is ignoring the HAVING.

我试图进行子选择，但是它也不起作用.

I tried to make a sub-select, but it doesn't work too.

谢谢您的回答.

推荐答案

在这里，您需要一个子查询，该子查询为表ps_order_history上的每个id_order获取最新的date_add.然后将子查询的结果重新合并到原始表ps_order_history上，条件是该子查询可以分为两列:date_add和id_order.

Here, you need to have a subquery which gets the latest date_add for every id_order on table ps_order_history. The result of the subquery is then joined back on the original table ps_order_history provided that it macth on two columns: date_add and id_order.

SELECT c.firstname, c.lastname, d.product_name, o.payment, s.name, h.date_add FROM ps_orders o INNER JOIN ps_order_detail d ON d.id_order = o.id_order INNER JOIN ps_customer c ON c.id_customer = o.id_customer INNER JOIN ps_order_history h ON o.id_order = h.id_order INNER JOIN ( SELECT id_order, MAX(date_add) max_date FROM ps_order_history GROUP BY id_order ) x ON h.id_order = x.id_order AND h.date_add = x.max_date INNER JOIN ps_order_state_lang s ON s.id_order_state = h.id_order_state WHERE s.id_lang =6 GROUP BY c.id_customer