2014.09.23 mysql 一条sql语句实现实现搜索功能（加权排序）

一、加权排序原理

二、转化为SQL语句(合理使用case when xx then x else x end 等mysql的函数)

搜索功能的实现：加权排序原理
1.每一个关键字去匹配标题、标签、摘要都会得到一个权重值，将这个权重值作为排序的依据； 2.如果输入多个关键字，则将每一个关键字匹配所得的权重值求积得到最终的权重值，这样做可以满足“多个关键字均被匹配的排在前面”，然后以这个最终的权重值来排序，具体情况如下。
权重默认值（0.1）
情况一：输入两个关键字k1,k2，其中k1匹配一篇文章的摘要，k2也匹配同一篇文章的摘要
匹配项	分值	k1	k2
标题	4	×	×
标签	3	×	×
摘要	2	√	√
权重值		0.1+0.1+2 = 2.2	0.1+0.1+2 = 2.2
最终权重值（求积）		2.2×2.2 = 4.84
最终权重值（求和）		2.2+2.2 = 4.4
情况二：输入两个关键字k1,k2，其中k1匹配一篇文章的标题、标签、摘要，k2不匹配任何一篇文章
匹配项	分值	k1	k2
标题	4	√	×
标签	3	√	×
摘要	2	√	×
权重值		4+3+2 = 9	0.1+0.1+0.1 = 0.3
最终权重值(求积)		9×0.3 = 2.7
最终权重值（求和）		9+0.3 = 9.3
情况三：输入两个关键字k1,k2，其中k1匹配一篇文章的标题、标签、摘要，k2匹配同一篇文章的摘要
匹配项	分值	k1	k2
标题	4	√	×
标签	3	√	×
摘要	2	√	√
权重值		4+3+2 = 9	0.1+0.1+2 = 2.2
最终权重值(求积)		9×2.2 = 19.8
最终权重值（求和）		9+2.2 = 11.2

SELECT
 1 * (
  document.w10 + document.w20 + document.w30
 ) * (
  document.w11 + document.w21 + document.w31
 ) AS w,
 document.*
FROM
 (
  SELECT
   0,
   CASE WHEN d.abstracts LIKE '%1%' THEN
    2 ELSE 0.1
    END AS w10,
   CASE WHEN d.tags LIKE '%1%' THEN 
    3 ELSE 0.1
    END AS w20,
   CASE WHEN d. NAME LIKE '%1%' THEN
    4 ELSE 0.1
    END AS w30,
   CASE WHEN d.abstracts LIKE '%2%' THEN
    2 ELSE 0.1
    END AS w11,
   CASE WHEN d.tags LIKE '%2%' THEN
    3 ELSE 0.1
    END AS w21,
   CASE WHEN d. NAME LIKE '%2%' THEN
    4 ELSE 0.1
    END AS w31,
   d.*
 FROM
  document d 
) document LEFT JOIN document dd
ON document.id = dd.id
 WHERE
 dd.sts = 'normal'
 AND to_days(now()) >= to_days(dd.protect_time)
 AND dd.create_time >= '2014-07-10 00:00:00'
 AND dd.create_time <= '2014-09-23 09:13:56'
 AND dd.category_id LIKE '0001%'
 AND dd.p_id LIKE '%'
 HAVING w > 0.09
 ORDER BY
 w DESC,
 dd.create_time DESC