mysql排序加权_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