如何修改SQL查询语句筛选指定属性值的商品并保留美化属性输出

最终修改后的查询语句（完全保留原有格式化输出逻辑） #

SELECT
    title,
    stock,
    price,
    (
        SELECT GROUP_CONCAT(p.title, ': ', pv.value SEPARATOR ', ') 
        FROM product_property_value pv 
        INNER JOIN property p on pv.property_id = p.id WHERE pv.product_id = product.id
    ) property_values
FROM product
WHERE 
-- 校验颜色为Red
EXISTS (
    SELECT 1 FROM product_property_value pv1
    INNER JOIN property p1 ON pv1.property_id = p1.id
    WHERE pv1.product_id = product.id AND p1.code = 'color' AND pv1.value = 'Red'
)
-- 校验宽度为4或5
AND EXISTS (
    SELECT 1 FROM product_property_value pv2
    INNER JOIN property p2 ON pv2.property_id = p2.id
    WHERE pv2.product_id = product.id AND p2.code = 'width' AND pv2.value IN ('4','5')
)
-- 校验高度为5
AND EXISTS (
    SELECT 1 FROM product_property_value pv3
    INNER JOIN property p3 ON pv3.property_id = p3.id
    WHERE pv3.product_id = product.id AND p3.code = 'height' AND pv3.value = '5'
);

说明 #

因为属性采用行式存储，每个属性对应商品关联表的一条独立记录，所以用EXISTS子查询分别校验三个属性条件，不会和原有拼接属性的子查询产生冲突，完全保留原有输出格式。
如果你确认property表的id固定不会修改，可以去掉子查询中关联property表的逻辑，直接校验property_id，性能会更高，比如颜色校验可以简化为：

EXISTS (SELECT 1 FROM product_property_value pv1 WHERE pv1.product_id = product.id AND pv1.property_id = 1 AND pv1.value = 'Red')

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