MySql多个字段关键字分词模糊查询,按照最佳匹配度排序,使用LOCATE函数
SELECT DISTINCT
t.*
FROM
pub_course t
LEFT JOIN course_tag t1 ON (
t1.entitytype = t.type
AND t1.entityid = t.id
)
WHERE
t.ishide != 1
AND t.pubstatus = 1
AND (
t. NAME LIKE '%盛放的彩铅%'
OR t. NAME = '盛放的彩铅'
OR t. NAME LIKE '%彩铅%'
OR t. NAME = '彩铅'
OR t. NAME LIKE '%盛放%'
OR t. NAME = '盛放'
OR t.createname LIKE '%盛放的彩铅%'
OR t.createname = '盛放的彩铅'
OR t1.tlname LIKE '%盛放的彩铅%'
OR t1.tlname = '盛放的彩铅'
OR t.createname LIKE '%彩铅%'
OR t.createname = '彩铅'
OR t1.tlname LIKE '%彩铅%'
OR t1.tlname = '彩铅'
OR t.createname LIKE '%盛放%'
OR t.createname = '盛放'
OR t1.tlname LIKE '%盛放%'
OR t1.tlname = '盛放'
)
ORDER BY
LOCATE('盛放的彩铅',t.name) DESC,
t.hotlevel DESC,
t.score DESC,
t.buynum DESC,
t.pubtime DESC