Magento 2产品附加图片数量统计SQL查询问题求助

原查询存在几个关键错误，导致无法正常运行：

• 关联catalog_product_entity_media_gallery_value时，错误使用media_value.value_id = product.entity_id，正确关联应为media_value.entity_id = product.entity_id（media_gallery_value表的entity_id对应产品的entity_id，value_id是关联media_gallery的主键）
• 通过additional_image_labels统计会遗漏未设置标签的附加图片，因为additional_image_labels是可选属性，并非所有附加图片都有标签

以下是两种场景的修正查询：

场景1：统计所有非主图的附加图片数量 #

SELECT 
    product.sku,
    COUNT(gallery.value_id) AS num_additional_images
FROM catalog_product_entity AS product
JOIN catalog_product_entity_media_gallery_value AS media_value 
    ON media_value.entity_id = product.entity_id
JOIN catalog_product_entity_media_gallery AS gallery 
    ON gallery.value_id = media_value.value_id
LEFT JOIN catalog_product_entity_varchar AS main_image 
    ON main_image.entity_id = product.entity_id
    AND main_image.attribute_id = (
        SELECT attribute_id 
        FROM eav_attribute 
        WHERE attribute_code = 'image' 
        AND entity_type_id = (
            SELECT entity_type_id 
            FROM eav_entity_type 
            WHERE entity_type_code = 'catalog_product'
        )
    )
WHERE gallery.value != main_image.value -- 排除主图
GROUP BY product.entity_id, product.sku;

场景2：统计带有additional_image_labels标签的图片数量 #

SELECT 
    product.sku,
    COUNT(additional_image_labels.value_id) AS num_additional_image_labels
FROM catalog_product_entity AS product
JOIN catalog_product_entity_media_gallery_value AS media_value 
    ON media_value.entity_id = product.entity_id
JOIN catalog_product_entity_media_gallery AS gallery 
    ON gallery.value_id = media_value.value_id
JOIN catalog_product_entity_varchar AS additional_image_labels 
    ON additional_image_labels.entity_id = product.entity_id
    AND additional_image_labels.value = gallery.value
WHERE additional_image_labels.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'additional_image_labels' 
    AND entity_type_id = (
        SELECT entity_type_id 
        FROM eav_entity_type 
        WHERE entity_type_code = 'catalog_product'
    )
)
GROUP BY product.entity_id, product.sku;

关键修正说明 #

• 修正了media_value与product的关联逻辑，使用entity_id建立正确关联
• 针对不同统计需求提供两种方案，覆盖“所有附加图”和“带标签附加图”场景
• 确保GROUP BY包含所有非聚合字段，避免SQL语法错误

内容的提问来源于stack exchange，提问作者heroecall