Magento 2如何通过join关联catalog_product_entity_decimal表获取special_price

由于price（attribute_id=77）和special_price（attribute_id=78）都存储在catalog_product_entity_decimal这张EAV表中，可以通过自连接的方式关联查询，参考SQL如下：

SELECT 
    p.entity_id AS product_id,
    p.value AS price,
    sp.value AS special_price
FROM catalog_product_entity_decimal p
LEFT JOIN catalog_product_entity_decimal sp
    ON p.entity_id = sp.entity_id 
    AND sp.attribute_id = 78
WHERE p.attribute_id = 77

补充说明 #

• 上述SQL使用LEFT JOIN是为了保留所有已设置price的产品数据，未设置special_price的产品对应字段会返回NULL，如果只需要查询有设置special_price的产品，可将LEFT JOIN替换为INNER JOIN
• 如果需要筛选对应店铺视图的价格，可在关联条件和WHERE条件中增加store_id参数过滤，全局默认店铺对应的store_id为0，示例：

SELECT 
    p.entity_id AS product_id,
    p.value AS price,
    sp.value AS special_price
FROM catalog_product_entity_decimal p
LEFT JOIN catalog_product_entity_decimal sp
    ON p.entity_id = sp.entity_id 
    AND sp.attribute_id = 78
    AND sp.store_id = 0
WHERE 
    p.attribute_id = 77
    AND p.store_id = 0

• 如果需要同时获取产品SKU等基础信息，可额外关联产品主表catalog_product_entity：

SELECT 
    cpe.sku,
    p.entity_id AS product_id,
    p.value AS price,
    sp.value AS special_price
FROM catalog_product_entity cpe
LEFT JOIN catalog_product_entity_decimal p
    ON cpe.entity_id = p.entity_id 
    AND p.attribute_id = 77
    AND p.store_id = 0
LEFT JOIN catalog_product_entity_decimal sp
    ON cpe.entity_id = sp.entity_id 
    AND sp.attribute_id = 78
    AND sp.store_id = 0

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