我需要在产品详细信息页面中显示类似的产品,这样列出的产品应该以分层方式与当前产品属性相匹配,如下所示
- 尺寸、颜色、类别、公司应与当前产品相匹配
- 尺寸、颜色、类别应与当前产品相匹配
- 尺寸、颜色应与当前产品相匹配
- 尺寸应与当前产品相匹配
我的sql查询如下所示:
(SELECT pd.product_id, pd.name, p.price
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.category_id = '3' AND
pd.company_id = '1' AND
pd.product_id != '53' AND
p.status = '1'
ORDER BY RAND() LIMIT 10
)
UNION
(SELECT pd.product_id, pd.name, p.price
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.category_id = '3' AND
pd.product_id != '53' AND
p.status = '1'
ORDER BY RAND() LIMIT 10
)
UNION
(SELECT pd.product_id, pd.name, p.price
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.product_id != '53' AND
p.status = '1'
ORDER BY RAND() LIMIT 10
)
UNION
(SELECT pd.product_id, pd.name, p.price
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.product_id != '53' AND
p.status = '1'
ORDER BY RAND() LIMIT 10
)
53-当前产品id和状态表示可用!
有什么方法可以优化上述查询吗?
注意需要输出:我们需要10个类似的产品。如果存在4个符合条件1的产品,则需要先按随机顺序列出。同样,我们需要在下面列出符合其他条件的产品。
提前感谢!
继续使用ORDER BY RAND()(这不是有效的),然后一个非常基本的改进是为每个子句添加优先级,将ORDER子句移动到末尾,这样只需要一次。
(SELECT pd.product_id, pd.name, p.price , 1 AS recpriority
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.category_id = '3' AND
pd.company_id = '1' AND
pd.product_id != '53' AND
p.status = '1'
)
UNION
(SELECT pd.product_id, pd.name, p.price , 2 AS recpriority
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.category_id = '3' AND
pd.product_id != '53' AND
p.status = '1'
)
UNION
(SELECT pd.product_id, pd.name, p.price , 3 AS recpriority
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.color_id = '2' AND
pd.product_id != '53' AND
p.status = '1'
)
UNION
(SELECT pd.product_id, pd.name, p.price , 4 AS recpriority
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE
pd.size_id = '33' AND
pd.product_id != '53' AND
p.status = '1'
)
ORDER BY recpriority, RAND() LIMIT 10
这样做就可以在不需要工会的情况下做到:-
SELECT pd.product_id,
pd.name,
p.price ,
CASE
WHEN pd.color_id = '2' AND pd.category_id = '3' AND pd.company_id = '1' THEN 1
WHEN pd.color_id = '2' AND pd.category_id = '3' THEN 2
WHEN pd.color_id = '2' THEN 3
ELSE 4
END AS recpriority
FROM mg_product_description pd
JOIN `mg_product` p ON p.product_id = pd.product_id
WHERE pd.size_id = '33'
AND pd.product_id != '53'
AND p.status = '1'
ORDER BY recpriority, RAND()
LIMIT 10