MySQL函数:按最相似的属性排序表

I have a table of products ids and keywords that looks like the following:

我有一个产品id和关键字的表格，如下所示:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| product_id | int(10) unsigned | YES  | MUL | NULL    |                |
| keyword    | varchar(255)     | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

This table simply stores product ids, and keywords associated with those products. So for example, it might contain:

此表仅存储产品id和与这些产品相关的关键字。例如，它可能包含:

+----+------------+---------+
| id | product_id | name    |
+----+------------+---------+
|  1 |         1  | soft    |
|  2 |         1  | red     |
|  3 |         1  | leather |
|  4 |         2  | cloth   |
|  5 |         2  | red     |
|  6 |         2  | new     |
|  7 |         3  | soft    |
|  8 |         3  | red     |
|  9 |         4  | blue    |
+----+------------+---------+

In other words:

换句话说:

• product 1 is soft, red, and leather.
• 产品一是柔软的，红色的和皮革的。
• product 2 is cloth, red and new.
• 产品二是布、红、新。
• Product 3 is red and soft,
• 产品3是红色和柔软的，
• product 4 is blue.
• 产品4是蓝色的。

I need some way to take in a product ID, and get back a sorted list of product ids ranked by the number of common keywords

我需要某种方式输入产品ID，然后返回按常用关键字数量排序的产品ID列表

So for example, if I pass in product_id 1, I'd expect to get back:

例如，如果我传入product_id 1，我希望返回:

+----+-------+------------+
| product_id | matches    |
+------------+------------+
|     3      | 2          | (product 3 has two common keywords with product 1)
|     2      | 1          | (product 2 has one common keyword with product 1)
|     4      | 0          | (product 4 has no common keywords with product 1)
+------------+------------+

2 个解决方案

#1

One option uses a self right outer join with conditional aggregation to count the number of matched names between, e.g. product ID 1, and all other product IDs:

一个选项使用一个自右外部连接，并使用条件聚合来计算匹配的名称(例如product ID 1)和所有其他产品ID的数量:

SELECT t2.product_id,
       SUM(CASE WHEN t1.name IS NOT NULL THEN 1 ELSE 0 END) AS matches
FROM yourTable t1
RIGHT JOIN yourTable t2
    ON t1.name = t2.name AND
       t1.product_id = 1
WHERE t2.product_id <> 1
GROUP BY t2.product_id
ORDER BY t2.product_id

Follow the link below for a running demo:

下面是运行演示的链接:

SQLFiddle

#2

You need to use an outer join against the keywords for productid 1:

您需要对productid 1的关键字使用外部连接:

select y.productid, count(y2.keyword)
from yourtable y 
  left join (
    select keyword from yourtable y2 where y2.productid = 1
    ) y2 on y.keyword = y2.keyword
where y.productid <> 1
group by y.productid
order by 2 desc

• SQL Fiddle Demo
• SQL小提琴演示

---

Results:

结果:

| productid | count(y2.keyword) |
|-----------|-------------------|
|         3 |                 2 |
|         2 |                 1 |
|         4 |                 0 |