我有一个产品数据库,这就是我正在运行查询以显示关键字"椅子"的结果:
SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE `product_name` LIKE '%chair%'
AND `products`.`active` = 1
UNION ALL
SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE `category_id` IN (128,135,136,138)
AND `products`.`active` = 1
我在此线程上看到了:mysql顺序"最佳匹配"。如何仅在%like%
约束下以"最佳匹配"订购。
如何在我的特定查询上实现类似的东西?
如果我不误解您所追求的内容,则可以尝试将订购语句放在最后。并在每个选择语句周围放置括号,以防止MySQL错误地应用订单,从第二个选择语句而不是整个联盟结果:
:(SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE `product_name` LIKE '%chair%'
AND `products`.`active` = 1)
UNION ALL
(SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE `category_id` IN (128,135,136,138)
AND `products`.`active` = 1)
ORDER BY
CASE
WHEN `product_name` LIKE 'chair%' THEN 1
WHEN `product_name` LIKE '%chair' THEN 3
ELSE 2
END