MySQL如何将查询返回的price、special_price两行合并为单行展示

完全可以实现，核心是避免单次JOIN同时匹配两个属性导致的行拆分，推荐采用拆分两次独立LEFT JOIN的方案，修改后的完整查询如下：

SELECT parent.entity_id AS parent_id,
    simple.entity_id AS simple_id,
    simple.sku AS simple_sku,
    cpp.rule_price AS rule_price,
    -- 分别取两个关联表的属性值作为独立字段
    cped_price.value AS price,
    cped_special.value AS special_price,
    cpev.value AS simple_name,
    parent.type_id AS type_id,
    wi.store_id AS wishlist_store_id
FROM catalog_product_entity AS parent
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id
LEFT JOIN catalogrule_product_price AS cpp ON cpp.product_id = simple.entity_id AND
    cpp.website_id = (SELECT `website_id` FROM `store` WHERE `store_id` = '13')
-- 单独关联原价属性
LEFT JOIN catalog_product_entity_decimal AS cped_price ON cped_price.row_id = simple.entity_id
    AND cped_price.attribute_id = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'price')
    AND cped_price.store_id IN (13,0)
-- 单独关联特惠价属性
LEFT JOIN catalog_product_entity_decimal AS cped_special ON cped_special.row_id = simple.entity_id
    AND cped_special.attribute_id = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'special_price')
    AND cped_special.store_id IN (13,0)
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpev.row_id = simple.entity_id
    AND cpev.store_id IN (13,0) AND cpev.attribute_id = (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` = 'name'
        order by `attribute_id` DESC
        LIMIT 1)
LEFT JOIN wishlist_item AS wi ON parent.row_id = wi.product_id AND `wishlist_id` = '1'
WHERE parent.entity_id = '9244'
-- 优先取store_id=13的属性值，避免同属性多store值重复
GROUP BY simple.entity_id
ORDER BY cped_price.store_id DESC, cped_special.store_id DESC

改动说明 #

1. 移除了原来同时匹配price和special_price两个属性的JOIN逻辑，避免同个商品生成两行记录
2. 拆分出两个独立的LEFT JOIN，分别对应原价、特惠价两个属性，各自单独关联属性值
3. 新增special_price字段输出特惠价，和原价price字段并列显示
4. 增加GROUP BY simple.entity_id保证同个子商品仅返回一行，同时保留store_id排序逻辑，优先取store_id=13的配置值，没有则自动取store_id=0的全局值

如果你使用的是MySQL 8.0及以上版本，也可以用窗口函数ROW_NUMBER()在子查询中预先过滤每个属性最高优先级的store值，避免分组导致的取值不准确问题。

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