MySQL查询实现：筛选商品时按选中属性取对应价格或返回基础价格

实现思路 #

1. 先筛选出同时拥有所有选中属性值的商品（你示例中选中的属性值为19、4，可根据实际需求替换成动态参数）
2. 匹配该商品是否存在与选中属性集合完全一致的定价规则
3. 用COALESCE优先取匹配到的定价，没有匹配则取商品基础价格

完整SQL语句 #

SELECT 
    p.product_id,
    p.product_name,
    COALESCE(ppi.price, p.price) AS final_price,
    COALESCE(ppi.price_name, '基础定价') AS price_type
FROM product p
-- 筛选同时拥有选中属性的商品，选中多少个属性就加多少个JOIN
JOIN eav_ent en1 ON en1.product_id = p.product_id AND en1.attr_value_id = 19
JOIN eav_ent en2 ON en2.product_id = p.product_id AND en2.attr_value_id = 4
-- 关联定价表
LEFT JOIN eav_ent_product_price ppi 
    ON ppi.product_id = p.product_id
-- 关联定价对应的属性，筛选定价绑定的属性在选中集合内的记录
LEFT JOIN eav_ent_product_price_attr pp_attr 
    ON pp_attr.ent_product_price_id = ppi.ent_product_price_id
    AND pp_attr.attr_value_id IN (19,4)
GROUP BY p.product_id, ppi.ent_product_price_id
-- 校验定价绑定的属性刚好全部匹配选中的属性，没有多余也没有缺失，没有匹配定价的记录也保留
HAVING COUNT(pp_attr.attr_value_id) = 2 -- 这里的2是选中属性的数量，和IN里的参数数量一致
OR ppi.ent_product_price_id IS NULL
-- 优先取属性匹配度最高的定价，每个商品只保留一条最优结果
ORDER BY p.product_id, COUNT(pp_attr.attr_value_id) DESC
LIMIT 1;

效果验证 #

按你示例中选中属性值19（对应M码）和4（对应带拉链）的场景：

• product_id=1的商品会命中定价ID=3，返回价格20
• product_id=2的商品会命中定价ID=5，返回价格25
• 如果是没有配置对应属性定价的商品，会自动返回product表中的基础价格

参数修改说明 #

如果后续要修改选中的属性组合，只需要调整三个位置：

1. 新增/删除eav_ent的JOIN语句，每个选中属性对应一个JOIN
2. 修改pp_attr.attr_value_id IN ()里的属性值列表
3. 修改HAVING里的计数，等于你选中的属性总个数

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