May
09
2024
mysql多词按权重匹配搜索
作者:
绝缘体.. 发布:
2024-05-09 17:27 分类:
未分类 阅读:
抢沙发
有四个中文词(中国,美国,法国,日本),请写一个sql语句,按排列组合的形式去数据库表查询,假设表名为article,要查询的字段名是 content,按匹配的词语多少依次显示结果,例如同时匹配到4个词的就放在最前面,仅匹配到其中一个词的排在最后面。
SELECT
content,
(
CASE
WHEN content LIKE '%中国%' AND content LIKE '%美国%' AND content LIKE '%法国%' AND content LIKE '%日本%' THEN 4
WHEN content LIKE '%中国%' AND content LIKE '%美国%' AND content LIKE '%法国%' OR
content LIKE '%中国%' AND content LIKE '%美国%' AND content LIKE '%日本%' OR
content LIKE '%中国%' AND content LIKE '%法国%' AND content LIKE '%日本%' OR
content LIKE '%美国%' AND content LIKE '%法国%' AND content LIKE '%日本%' THEN 3
WHEN content LIKE '%中国%' AND content LIKE '%美国%' OR
content LIKE '%中国%' AND content LIKE '%法国%' OR
content LIKE '%中国%' AND content LIKE '%日本%' OR
content LIKE '%美国%' AND content LIKE '%法国%' OR
content LIKE '%美国%' AND content LIKE '%日本%' OR
content LIKE '%法国%' AND content LIKE '%日本%' THEN 2
WHEN content LIKE '%中国%' OR content LIKE '%美国%' OR content LIKE '%法国%' OR content LIKE '%日本%' THEN 1
ELSE 0
END
) AS match_count
FROM
article
ORDER BY
match_count DESC, content;
微信扫一扫,打赏作者吧~