按ID分组，其最大值为(date_field) [英] Group by id having max(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

这篇关于按ID分组，其最大值为(date_field)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！