admin

按ID分组,其最大值为(date_field)

sql

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

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

我试图进行子选择,但是它也行不通。

谢谢你的回答。


阅读 37

收藏
2021-06-07

共1个答案

admin

在这里,您需要一个子查询,该子查询获取date_add每个id_orderon
table的最新信息ps_order_history。然后将子查询的结果重新ps_order_history合并到原始表中,前提是它可以分为两列:date_addid_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
2021-06-07