php - MySQL:使用 GROUP BY 时显示最新值

Question

我的表“orders”包含 date_purchased，表“orders_products”包含特定订单的 products_id。

我想列出特定客户的所有购买的 products_id(不是所有订单!)，显示每个 products_id 的最新购买日期。该列表应按最新的orders_id 排列在顶部。

下面的代码将显示我想要的所有唯一的 products_id，但“分组依据”导致不显示每个 products_id 的最新orders_id或date_purchased…

我在这里缺少什么？

SELECT o.orders_id, o.date_purchased, op.products_id

FROM orders o, orders_products op

WHERE o.customers_id = '" . $client_id . "' and op.orders_id = o.orders_id

GROUP BY op.products_id
ORDER BY orders_id DESC

Answer 1

不存在方法通常是此类查询最有效的方法:

SELECT o.orders_id, o.date_purchased, op.products_id 
FROM orders o join
     orders_products op
     on op.orders_id = o.orders_id
WHERE o.customers_id = '" . $client_id . "' and 
      not exists (select 1
                  from orders o2 join
                       orders_products op2
                       on op2.orders_id = o2.orders_id
                  where op2.products_id = op.products_id and
                        o.customers_id = '" . $client_id . "' and
                        o2.orders_id > o.orders_id
                 )
ORDER BY orders_id DESC;

逻辑是:“从 orders 中获取所有行，其中不存在具有相同产品和较大 ID 的行。”这相当于说:“给我最大行”。

为了获得最佳性能，您需要在 orders(products_id,orders_id) 上建立索引。

编辑:

还有另一种方法使用 subtring_index() 和 group_concat()。如果 customer_id 上的过滤器具有高度选择性(即大大减少行数)，这可能是最有效的方法。

SELECT max(o.orders_id) as orders_id,
       substring_index(group_concat(o.date_purchased order by orders_id desc), ',', 1) as date_purchased,
       op.products_id 
FROM orders o join
     orders_products op
     on op.orders_id = o.orders_id
WHERE o.customers_id = '" . $client_id . "'
GROUP BY op.products_id;

当然，如果购买日期和 orders_id 都在增加，您可以将其简化为对两者使用 max():

SELECT max(o.orders_id) as orders_id,
       max(o.date_purchased) as date_purchased,
       op.products_id 
FROM orders o join
     orders_products op
     on op.orders_id = o.orders_id
WHERE o.customers_id = '" . $client_id . "'
GROUP BY op.products_id;