SQL 模糊查询后按匹配度排序 CASE WHEN THEN

蔚落 2022-01-28 19:23 526阅读 0赞

表如下:

20190529210058380.png

SELECT * FROM “table1” where name like ‘%玉米%’

很明显这个会返回所有的行,因为每行都满足条件

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3djYzI3ODU3Mjg1_size_16_color_FFFFFF_t_70

若我想在模糊查询后,进行排序,完全匹配的放在最前面,其余的放后面,可以使用case when then

如:

SELECT * FROM “table1” WHERE name LIKE ‘%玉米%’
ORDER BY (
CASE
WHEN name=’玉米’ THEN 1
WHEN name like ‘玉米%’ THEN 2
WHEN name like ‘%玉米%’ THEN 3
WHEN name like ‘%玉米’ THEN 4
ELSE 5
END
)

顺序是:完全匹配>开头匹配>中间匹配>最后匹配

else后面的数字代表剩余的情况则按ELSE后的数字顺序排,5>4,所以剩余的情况会排在最后

查询结果如图:

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3djYzI3ODU3Mjg1_size_16_color_FFFFFF_t_70 1

假设表里有多个字段,同时模糊查询多个字段,但是字段之间也有优先级排序,

比如name>nickname>code,然后再按完全匹配>开头匹配>中间匹配>最后匹配的顺序排

表如下:

20190529211346998.png

那么我可以这样,sql语句如下:

SELECT * FROM “table1” WHERE name LIKE ‘%yumi%’
OR nickname LIKE ‘%yumi%’
OR code LIKE ‘%yumi%’

ORDER BY (
CASE
WHEN name=’yumi’ THEN 1
WHEN name like ‘yumi%’ THEN 2
WHEN name like ‘%yumi%’ THEN 3
WHEN name like ‘%yumi’ THEN 4

WHEN nickname=’yumi’ THEN 5
WHEN nickname like ‘yumi%’ THEN 6
WHEN nickname like ‘%yumi%’ THEN 7
WHEN nickname like ‘%yumi’ THEN 8

WHEN code=’yumi’ THEN 9
WHEN code like ‘yumi%’ THEN 10
WHEN code like ‘%yumi%’ THEN 11
WHEN code like ‘%yumi’ THEN 12

ELSE 13
END
)

如图:

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3djYzI3ODU3Mjg1_size_16_color_FFFFFF_t_70 2

这个表格的数据不是十分贴切,但是大致是这样意思

发表评论

表情:
评论列表 (有 0 条评论,526人围观)

还没有评论,来说两句吧...

相关阅读