GROUP BY，ORDER BY-如何根据项目的最新外观进行分组 [英] GROUP BY, ORDER BY - How to make group by consider latest apperance of item

问题描述

我有这样的查询(Mysql 5.X，PHP-出于可读性考虑对其进行了格式化)

I have a query like this (Mysql 5.X, PHP - formatted for legibility)

$query ="
SELECT 
  p.p_pid, 
  p.p_name, 
  p.p_url 
FROM 
  activity a, 
  products p 
WHERE 
  a.a_uid= ".$uid_int." 
  AND a.a_pid > 0 
  AND p.p_pid = a.a_pid 
GROUP BY 
  a.a_pid 
ORDER BY 
  a.a_time DESC LIMIT 6
");

通常，它应该生成用户已看到的6种最新产品的唯一列表.

In general it should produce a unique list of the 6 latest products the user has seen.

问题在于，如果用户多次浏览产品.其中一个位于最近6个活动中，另一个位于最近6个活动之前，查询不会返回产品.我假设(group by)不会将a_time与产品出现的最晚时间分开.我该如何纠正?

The problem is that if the user has seen a product more than once. one of them in the last 6 activities and one of them before the latest 6 activities the query does not return the product. I assume that the (group by) does not leave a_time with the latest time of apperance of the product. How can I correct it?

推荐答案

您是否尝试过通过MAX(a.a_time)订购?

Have you tried ordering by MAX(a.a_time) ?

SELECT 
  p.p_pid, 
  p.p_name, 
  p.p_url 
FROM products p 
INNER JOIN activity a on p.p_pid = a.a_pid 
WHERE 
  a.a_uid= ".$uid_int." 
GROUP BY 
  p_pid, p_name, p_url
ORDER BY 
  max(a.a_time) DESC 
  LIMIT 6

最佳做法是，对使用的每个列都使用GROUP BY，但不要使用汇总. MySQL是少数几个允许您使用未分组的列的数据库之一.它将为您提供所选行中的随机列.

As a best practice, use GROUP BY on every column you use without an aggregate. MySQL is one of the few databases that allow you to use a column that's not being grouped on. It'll give you a random column from the rows you selected.

这篇关于GROUP BY，ORDER BY-如何根据项目的最新外观进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！