mysql - 按相关性标签搜索

Question

我找不到这样的东西

我有 Sql 表

tag (tag_id, tag_name..)
tags (id, tag_id, post_id)

例子:

table: tag
1 New York
2 Madrid
3 Paris
4 London

如果我以某些帖子包含 0、1、2、3 或多个标签的方式填充标签表，如何使 sql 具有搜索顺序以获得按帖子包含的标签数排序的结果？就像“最佳匹配”搜索..

例如，如果我搜索 3 个标签“纽约”、“马德里”、“巴黎”

导出的结果应该是>

Post_id's:  1,4,6,   2,3,5,   9
Where show first
    1,4,6 (has all 3 searched tags)
    then
    2,3,5 (has 2 searched tags)
    then 
    9 (with 1 searched tag)

Answer 1

试试这个:

SELECT tags.post_id, COUNT(*) AS match_count
FROM tag
JOIN tags ON tag.tag_id = tags.tag_id
WHERE tag.tag IN ('New York', 'Madrid', 'Paris')
GROUP BY tags.post_id
ORDER BY COUNT(*) DESC, tags.post_id ASC;

给出这些结果:

+---------+-------------+
| post_id | match_count |
+---------+-------------+
|       1 |           3 |
|       4 |           3 |
|       6 |           3 |
|       2 |           2 |
|       3 |           2 |
|       5 |           2 |
|       9 |           1 |
+---------+-------------+

有了这些数据:

CREATE TABLE tag (
    tag_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    tag VARCHAR(40)
);

INSERT INTO tag VALUES
(null, 'New York'),
(null, 'Madrid'),
(null, 'Paris'),
(null, 'London');

CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    tag_id INTEGER,
    post_id INTEGER
);

INSERT INTO tags VALUES
(null, 1, 1),
(null, 2, 1),
(null, 3, 1),
(null, 1, 4),
(null, 2, 4),
(null, 3, 4),
(null, 1, 6),
(null, 2, 6),
(null, 3, 6),
(null, 1, 2),
(null, 2, 2),
(null, 1, 3),
(null, 2, 3),
(null, 1, 5),
(null, 2, 5),
(null, 1, 9);