我有点像MySQL菜鸟,一直在尝试从Magento中提取产品列表和多个属性。 我目前有以下查询,它拉取属性 185 和 180。 但是,这些是在单独的行中输出的。 我想编辑查询,以便每个产品的属性都在一行上。 我的查询是:
SELECT
ce.sku AS sku,
ce_decimal.value AS attribute1,
ce_int.value AS attribute2
FROM catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea
ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_decimal AS ce_decimal
ON ce.entity_id = ce_decimal.entity_id
AND ea.attribute_id = ce_decimal.attribute_id
AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_int AS ce_int
ON ce.entity_id = ce_int.entity_id
AND ea.attribute_id = ce_int.attribute_id
AND ea.backend_type = 'int'
WHERE ce_decimal.attribute_id = '185'
OR ce_int.attribute_id = '180'
给出以下结果:
_______________________________________
sku attribute1 attribute2
_______________________________________
543 NULL 105
543 34.95 NULL
768 NULL 104
768 15.67 NULL
_______________________________________
我正在寻找的结果是:
_______________________________________
sku attribute1 attribute2
_______________________________________
543 34.95 105
768 15.67 104
_______________________________________
我需要使用GROUP_CONCAT或类似的东西吗? 这让我发疯了。 谢谢!
你可以
用GROUP BY
来压扁它:
SELECT sku,
MAX(attribute1) AS attribute1,
MAX(attribute2) AS attribute2
FROM (
SELECT
ce.sku AS sku,
ce_decimal.value AS attribute1,
ce_int.value AS attribute2
FROM catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea
ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_decimal AS ce_decimal
ON ce.entity_id = ce_decimal.entity_id
AND ea.attribute_id = ce_decimal.attribute_id
AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_int AS ce_int
ON ce.entity_id = ce_int.entity_id
AND ea.attribute_id = ce_int.attribute_id
AND ea.backend_type = 'int'
WHERE ce_decimal.attribute_id = '185'
OR ce_int.attribute_id = '180'
) AS sub
GROUP BY sku;
如果每个 SKU 有多个值,请使用 GROUP_CONCAT
:
SELECT sku,
GROUP_CONCAT(attribute1) AS attribute1,
GROUP_CONCAT(attribute2) AS attribute2
SqlFiddleDemo