Home > Backend Development > PHP Tutorial > mysql 按相似度来排序

mysql 按相似度来排序

WBOY
Release: 2016-06-06 20:45:44
Original
1436 people have browsed it

数据库tag字段内容格式:a,b,c,d ...
假设有这几条记录
1:刘世允
2:郑胜浩
3:申东烨
4: 申东烨,郑胜浩,刘世允,安英美
5: 郑胜浩,刘世允,安英美
6: 申东烨,郑胜浩
7:刘世允,安英美

给出的查询tag
$tag = '申东烨,郑胜浩,刘世允,安英美,李尚勋';
按照相同词的个数排序

查询的结果
1: 申东烨,郑胜浩,刘世允,安英美
2: 郑胜浩,刘世允,安英美
3: 申东烨,郑胜浩
4:刘世允,安英美
5:刘世允
6:郑胜浩
7:申东烨

回复内容:

数据库tag字段内容格式:a,b,c,d ...
假设有这几条记录
1:刘世允
2:郑胜浩
3:申东烨
4: 申东烨,郑胜浩,刘世允,安英美
5: 郑胜浩,刘世允,安英美
6: 申东烨,郑胜浩
7:刘世允,安英美

给出的查询tag
$tag = '申东烨,郑胜浩,刘世允,安英美,李尚勋';
按照相同词的个数排序

查询的结果
1: 申东烨,郑胜浩,刘世允,安英美
2: 郑胜浩,刘世允,安英美
3: 申东烨,郑胜浩
4:刘世允,安英美
5:刘世允
6:郑胜浩
7:申东烨

----------------建表

1

2

3

<code class="lang-sql">mysql> create table testsort (tag varchar(100) charset "GBK");

Query OK, 0 rows affected (0.51 sec)

</code>

Copy after login

```sql
mysql> describe testsort
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| tag | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.07 sec)

1

2

3

4

5

6

7

8

9

10

11

12

<code><br>----------------插入数据

```sql

mysql> insert into testsort(id,tag) values(1,"刘世允"),

    -> (2,"郑胜浩"),

    -> (3,"申东烨"),

    -> (4,"申东烨,郑胜浩,刘世允,安英美"),

    -> (5,"郑胜浩,刘世允,安英美"),

    -> (6,"申东烨,郑胜浩"),

    -> (7,"刘世允,安英美");

Query OK, 7 rows affected (0.07 sec)

Records: 7  Duplicates: 0  Warnings: 0

</code>

Copy after login

----------------方法

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

<code class="lang-sql">mysql> select id,

    -> tag,

    -> char_length(tag)-char_length(replace(tag,',',''))+1 as cnt

    -> from testsort;

+------+-----------------------------------------+------+

| id   | tag                                     | cnt  |

+------+-----------------------------------------+------+

|    1 | 刘世允                                  |    1 |

|    2 | 郑胜浩                                  |    1 |

|    3 | 申东烨                                  |    1 |

|    4 | 申东烨,郑胜浩,刘世允,安英美             |    4 |

|    5 | 郑胜浩,刘世允,安英美                    |    3 |

|    6 | 申东烨,郑胜浩                           |    2 |

|    7 | 刘世允,安英美                           |    2 |

+------+-----------------------------------------+------+

7 rows in set (0.00 sec)

 

</code>

Copy after login

----------------另外注意
Responses below will get you there. However, don't forget to use CHAR_LENGTH() instead of LENGTH() if you're using multibyte characters. – inhan Sep 10 '12 at 3:03

http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template