mysql - 按 ID 过滤查询

Question

我正在尝试检索所有产品信息，并按产品类别 ID 进行过滤。结果应该是数百个产品，但只返回 5 个项目。我的查询有什么问题吗？

SELECT product.id_product, 
product.reference,  
product.price,
product.active, 
product.quantity,  
product.id_category_default, 
lang.name AS product_name, 
lang.description, 
lang.link_rewrite AS product_link_rewrite,  
category.name AS category_name, 
category.link_rewrite AS category_link_rewrite, 
image.id_image, 
product.id_manufacturer, 
discount.reduction, 
manufacturer.name AS manufacturer_name 
FROM ps_product product 
LEFT JOIN ps_specific_price discount ON discount.id_product=product.id_product
LEFT JOIN ps_product_lang lang ON lang.id_product=product.id_product
LEFT JOIN ps_category_lang category ON category.id_category=product.id_category_default 
LEFT JOIN ps_image image ON image.id_product=product.id_product
LEFT JOIN ps_manufacturer manufacturer ON manufacturer.id_manufacturer=product.id_manufacturer
WHERE product.active=1 
AND product.quantity>=1 
AND product.id_category_default IN 
(
  4,5,6,65,66,90,91,53,54,48,49,50,55,62,67,68,71,19,82,88,89,87,22,24,26,74,
  76,77,28,78,79,97,98,99,93,96,35,36,38,39,100
)
GROUP BY product.id_product ASC

Answer 1

也许事情很简单:

按产品.id_产品 ASC 分组需要是:按产品.id_产品 ASC 排序

mySQL 扩展了 group by 子句，以便不在 group by 中的任何列都可以获得不确定的值。

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. ... The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

选项:

• 将分组依据更改为排序依据
• 将所有列添加到分组依据
• 根本不要使用分组依据。