例如有数据 美食成都好吃网 ,美食XX,XX好吃比如我搜索 美食成都 ,
我现在把美食成都给拆词了,就去数据库中查找含有:美食成都,美食,食成,成都,
从数据库从查询出来,放在数组里面,按照匹配程度最高的排序
对以上数据排序,就是 美食成都好吃网,美食XX,XX好吃(后面到无所谓,主要是前几个匹配一定要精确)我想的是以查询出来的数组,对数组进行排序,考虑到效率,我放弃了我用的是php+mysql我现在对词已经拆了,就是排序,困扰了好久了
我现在把美食成都给拆词了,就去数据库中查找含有:美食成都,美食,食成,成都,
从数据库从查询出来,放在数组里面,按照匹配程度最高的排序
对以上数据排序,就是 美食成都好吃网,美食XX,XX好吃(后面到无所谓,主要是前几个匹配一定要精确)我想的是以查询出来的数组,对数组进行排序,考虑到效率,我放弃了我用的是php+mysql我现在对词已经拆了,就是排序,困扰了好久了
where title like '%美食%' or title like '%成都%'
order by
(case when title like '%美食%' and title like '%成都%' then 0 else 1 end), /* 同时含有 美食+成都 排最前 */
(case when title like '%美食%' then 0 else 1 end), /* 只有 美食 排其次 */
(case when title like '%成都%' then 0 else 1 end) /* 只有 成都 排最后 */
;
where title like '%美食%' or title like '%成都%' or title like '%好吃%'
order by
(
(case when title like '%美食%' then 1 else 0 end)+
(case when title like '%成都%' then 1 else 0 end)+
(case when title like '%好吃%' then 1 else 0 end)
)
desc
;
/* 对关键词匹配的个数越多,排在越前 */
where title like '%美食%' or title like '%成都%' or title like '%好吃%'
order by
(
(case when title like '%美食%' then 1 else 0 end)+
(case when title like '%成都%' then 1 else 0 end)+
(case when title like '%好吃%' then 1 else 0 end)
)
desc
;
/* 对关键词匹配的个数越多,排在越前 */
FROM `food@roomding` AS t1, `food@points` AS t2, `food@roomtype` AS t3, `food@fee` AS t4
WHERE t1.RoomPoints = t2.ID
AND t1.RoomType = t3.ID
AND t1.RoomFee = t4.ID
AND (
t1.KeyWord LIKE '%锦里香酒楼%'
OR t1.KeyWord LIKE '%锦里%'
OR t1.KeyWord LIKE '%里香%'
OR t1.KeyWord LIKE '%香酒%'
OR t1.KeyWord LIKE '%酒楼%'
)
ORDER BY (CASE WHEN t1.KeyWord LIKE '锦里香酒楼'
THEN 0
ELSE 1
END
) + (
CASE WHEN t1.KeyWord LIKE '锦里'
THEN 0
ELSE 1
END ) + (
CASE WHEN t1.KeyWord LIKE '里香'
THEN 0
ELSE 1
END ) + (
CASE WHEN t1.KeyWord LIKE '香酒'
THEN 0
ELSE 1
END ) + (
CASE WHEN t1.KeyWord LIKE '酒楼'
THEN 0
ELSE 1
END ) desc LIMIT 0 , 30
晕,不行,比如我数据里面有 一个 锦里香
按道理说,这个匹配最高 确排在最后几个
含有酒楼两个字的,还是在第一
我用过ASC 也不行
我的第二个例子是 then 1 else 0
你却还是 then 0 else 1
应为 LIKE '%锦里香酒楼%'你试下
SELECT * , t2.Name AS Points, t3.Name AS RoomType, t4.Name AS fee
FROM `food@roomding` AS t1, `food@points` AS t2, `food@roomtype` AS t3, `food@fee` AS t4
WHERE t1.RoomPoints = t2.ID
AND t1.RoomType = t3.ID
AND t1.RoomFee = t4.ID
AND (
t1.KeyWord LIKE '%锦里香酒楼%'
OR t1.KeyWord LIKE '%锦里%'
OR t1.KeyWord LIKE '%里香%'
OR t1.KeyWord LIKE '%香酒%'
OR t1.KeyWord LIKE '%酒楼%'
)
ORDER BY ( CASE WHEN t1.KeyWord LIKE '%锦里香酒楼%'
THEN 1
ELSE 0
END
) + (
CASE WHEN t1.KeyWord LIKE '%锦里%'
THEN 1
ELSE 0
END ) + (
CASE WHEN t1.KeyWord LIKE '%里香%'
THEN 1
ELSE 0
END ) + (
CASE WHEN t1.KeyWord LIKE '%香酒%'
THEN 1
ELSE 0
END ) + (
CASE WHEN t1.KeyWord LIKE '%酒楼%'
THEN 1
ELSE 0
END ) desc LIMIT 0 , 30