要生成报告,我必须选择一些有关所有客户的最近交易状态的信息.到现在为止,这就是我得到的:
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更多推荐
按ID分组,其最大值为(date
发布评论