如何按相关性排序mysql搜索结果? [英] How to sort mysql search result by relevance?

问题描述

我有与此问题类似的查询

I have a similar query as asked on this question

MySQL-如何按相关性排序? INNODB表

区别是，这里我想从5个字段中搜索

Difference is, here I want to search from 5 fields as

add1，add2，add3，post_town，post_code

并且只有 post_code 字段中的记录不会为空，其他字段的记录在某些地方可能为空.如果我搜索关键字 kingston ，它将返回

And only records in post_code field will be NOT EMPTY, other fields records may be empty at some places. If I search for keyword kingston, it returns

Acre Road, Kingston upon Thames, KT2 6EA

Kingston Road, Epsom, KT19 0DG

Kingston Road, Epsom, KT20 0DH

这些结果是所有字段的组合 add1，add2，add3，post_town，post_code

and these results are combination of all fields add1, add2, add3, post_town, post_code

我需要以下顺序的结果

Kingston Road, Epsom, KT19 0DG

Kingston Road, Epsom, KT20 0DH

Acre Road, Kingston upon Thames, KT2 6EA

我当前的SQL查询就是这样

My Current SQL query is like this

SELECT add1, add2, add3, post_town, post_code FROM address_mst 
WHERE add1 LIKE '%".$keyword."%'
OR add2 LIKE '%".$keyword."%'
OR add3 LIKE '%".$keyword."%'
OR post_town LIKE '%".$keyword."%'
OR post_code LIKE '%".$keyword."%'

因此，我需要搜索关键字开头的记录排在第一位.我该怎么办?

推荐答案

不是最漂亮，但应该可以！

Not the prettiest but should work!

select a.* from (
SELECT add1, add2, add3, post_town, post_code, 1 as rank FROM address_mst 
WHERE add1 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 2 as rank FROM address_mst 
WHERE add2 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 3 as rank FROM address_mst 
WHERE add3 LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 4 as rank FROM address_mst 
WHERE post_town LIKE '%".$keyword."%'
UNION
SELECT add1, add2, add3, post_town, post_code, 5 as rank FROM address_mst 
WHERE post_code LIKE '%".$keyword."%'
) a
order by a.rank asc;

这篇关于如何按相关性排序mysql搜索结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！