MySQL按照满足条件数排序

问题

假设有一个MySQL表,有字段field1,field2,field3,我需要优先找到 field1=3 AND field2=2 AND field3=1 的记录,如果没有三个条件全满足的,满足任意两个条件也可,其次1个条件。

解决

这种查询并不是MySQL擅长的,用elasticsearch一类的搜索引擎会根据条件满足的程度计算评分,从而获得评分从高到低的列表,还可以设置某个字段的权重,对满足某个字段下的条件优先排序。虽然搜索引擎有诸多好处,但是并不是所有的应用都有搜索引擎服务,所以退而求其次,用MySQL如何达到同样的效果呢。

我们可以借鉴评分的方式,如果某个条件满足则获得评分,否则为0分。用MySQL的CASE语句来判断,那么评分规则的SQL语句就是:

ORDER BY ((CASE WHEN field1=3 THEN 1 ELSE 0 END)
    +(CASE WHEN field2=2 THEN 1 ELSE 0 END)
    +(CASE WHEN field3=1 THEN 1 ELSE 0 END)) DESC

这个语句实在有点长,看起来也费劲,其实就是类似C语言的三目运算,好在MySQL提供了一个类似三目运算的函数:IF(expression ,expr_true, expr_false);  第一个参数是布尔语句,第二个为true时的返回,第三个为false时的返回

把上面的语句用IF函数简化以后就是下面的样子:

ORDER BY (IF(field1=3, 1, 0)
    +IF(field2=2, 1, 0)
    +IF(field3=1, 1, 0)) DESC

而且第二个参数那里还可以设置权重,现在三个字段是等价的。

不过这样做的排序效率并不高,所以在 WHERE 子句中要先做好一层的数据过滤工作,避免大量数据参与排序。

终局

其实呢,这个需求有个更好的解决方式是用elasticsearch。这类场景本质上是根据匹配度或者说相似度排序,搜索引擎天生就是为这种场景而生的。

发表评论