MySQL：使用GROUP BY时显示最新值

Question

My table "orders" includes the date_purchased and my table "orders_products" includes the products_id for the specific order.

I want to list a specific client's all purchased products_id (not all orders!) showing the latest date_purchased for each products_id. The list should be ordered with the latest orders_id of these at the top.

The code below will show all unique products_id as I want, but the "group by" is resulting in not showing the latest orders_id or date_purchased for each products_id…

What am I missing here?

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

The not exists approach is often the most efficient approach for this type of query:

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;

The logic is: "Get me all rows from orders where there is no row with the same product and a larger id." This is equivalent to saying: "Get me the max row".

For best performance, you want an index on orders(products_id, orders_id).

EDIT:

There is another approach that uses subtring_index() and group_concat(). This might be the most efficient way, if the filter on customer_id is highly selective (that is, greatly reduces the number of rows).

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;

Of course, if the date purchased and orders_id are both increasing, you can simplify this to using max() for both:

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;