我有一个句子,这是在数据库中搜索。我删除了停止词,并将其拆分为变量中的关键字数组,即$key[0],$key[1],$key[2]。我想按照关键字最大匹配数的顺序检索数据,有办法吗?.
请帮助。
现在我使用
SELECT *
FROM 'table'
WHERE ('colomn' LIKE '%$key1%'
AND 'colomn' LIKE '%$key2%'
AND 'colomn' like '%$key3%'
);
如果没有结果
SELECT *
FROM 'table'
WHERE ('colomn' LIKE '%$key1%'
OR 'colomn' LIKE '%$key2%'
OR 'colomn' LIKE '%$key3%'
);
MySQL在这方面很可爱:
SELECT *, (colomn like "%$key1%") + (colomn like "%$key2%") + (colomn like "%$key3%") as match_rate
FROM table
WHERE (colomn like "%$key1%" or colomn like "%$key2%" or colomn like "%$key3%")
ORDER BY match_rate DESC;
不要在表名和列名之间使用单引号,而要使用反引号
这样的 Select * from `table` where (`colomn`....
代替
Select * from 'table' where ('colomn'........
使用第二个查询OR
我认为你在寻找类似的东西
order by case
when `colomn` LIKE "%$key1%" and `colomn` LIKE "%$key2%" and `colomn` LIKE "%$key3%" then 1
when (`colomn` LIKE "%$key1%" and `colomn` LIKE "%$key2%") or (`colomn` LIKE "%$key1%" and `colomn` LIKE "%$key3%") or (`colomn` LIKE "%$key3%" and `colomn` LIKE "%$key2%") then 2
else 3
end