现在,在我问这个之前...让我说我已经像以前一样搜索并尝试了几件事,这些事情似乎不起作用,我不知道我做错了什么。
我有一个网站,用户(具有不同的"owner_id")可以列出一个或多个产品。在一个特殊页面上,我只想为每个"owner_id"显示一个产品,这应该是最新的产品。
我尝试了 DISTINCT 查询,但由于某种原因它仍然返回所有产品。我做错了什么?在这里:
$sql_select_recent_items = $db->query("SELECT DISTINCT owner_id, start_time, name, price, owner_id
FROM db_products
FORCE INDEX (products_start_time)
WHERE closed=0 AND active=1 AND approved=1 AND deleted=0 AND creation_in_progress=0
ORDER BY start_time DESC;
我也试过:
$sql_select_recent_items = $db->query("SELECT DISTINCT(owner_id), start_time, name, price, owner_id
FROM db_products
FORCE INDEX (products_start_time)
WHERE closed=0 AND active=1 AND approved=1 AND deleted=0 AND creation_in_progress=0
ORDER BY start_time DESC;
以上两种似乎都不起作用。我做错了什么?我只想显示最新的项目(按start_time DESC订购),并且每个"owner_id"仅限于1个项目。
尝试:
SELECT DISTINCT(owner_id), start_time, name, price
FROM db_products
WHERE closed=0 AND active=1 AND approved=1 AND deleted=0 AND creation_in_progress=0
group by owner_id
ORDER BY start_time DESC;
也许
SELECT owner_id, MIN(start_time), name, price
FROM db_products
WHERE closed=0 AND active=1 AND approved=1 AND deleted=0 AND creation_in_progress=0
GROUP BY owner_id
如果您希望按start_id对最终列表(owner_id、start_id、名称、价格)进行排序,可以添加ORDER BY start_time DESC
。
我发现了这个问题。我需要在 GROUP BY 之前执行 ORDER BY,这通常是不可能的。可以在此处找到该问题的解决方案:
http://arfeen.net/2012/10/mysql-use-of-group-after-order-by/