mysql - INNER JOIN 中的 SELECT 和 Count

Question

我尝试显示每个产品的图像数量。但这里是请求返回所有产品的图像数量。

SELECT images, pl.*, l.iso_code, ps.price, cl.name as category_name
FROM `ps_product_lang` pl
INNER JOIN `ps_product` p ON (p.`id_product` = pl.`id_product`)
INNER JOIN `ps_lang` l ON (pl.`id_lang` = l.`id_lang`)
INNER JOIN `ps_product_shop` ps ON (ps.`id_product` = p.`id_product`)
INNER JOIN `ps_category_lang` cl ON (cl.`id_category` = p.`id_category_default`) and (cl.`id_lang` = pl.`id_lang`)
 
INNER JOIN (SELECT id_image, id_product, COUNT(id_image) AS images
FROM `ps_image`) i ON (i.`id_product` = p.id_product)
 
WHERE p.active = 1 AND cl.id_shop = 1
ORDER BY pl.name ASC
LIMIT 10 OFFSET 0

在这一行。

INNER JOIN (SELECT id_image, id_product, COUNT(id_image) AS images
FROM `ps_image`) i ON (i.`id_product` = p.id_product)

代码sql https://www.sexy-charmes.fr/sql.sql

Answer 1

此查询格式错误:

(SELECT id_image, id_product, COUNT(id_image) AS images
 FROM ps_image
) 

它是糟糕的 SQL，应该因语法错误而被拒绝。令人高兴的是，MySQL 现在可以使用默认设置执行此操作。

问题是什么？由于 COUNT()，您有一个聚合查询。没有 GROUP BY，因此查询只返回一行。但是那些行也有“裸”列。这是一个 SQL 语法错误，几乎所有数据库都存在错误。

您真的只想按一列聚合。这应该是唯一没有聚合函数的列选择，它应该在 GROUP BY 中:

(SELECT id_product, COUNT(id_image) AS images
 FROM ps_image
 GROUP BY id_product
)