mysql 按相似性來排序

資料庫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：申東燁

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

mysql> create table testsort (tag varchar(100) charset "GBK");Query OK, 0 rows affected (0.51 sec)

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

----------------插入資料```sqlmysql> 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

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

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)

----------------另外注意
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

