mysql - SQL 进行简单的相关性排名

Question

我有3个表:person_tag(person_id，tag)，interest_tag(interest_id，tag)，skill_tag(skill_id，tag)。尽管我希望有一张兴趣和技能标签表，但我需要将它们分开。示例数据如下:

+-----------+-------------+
| person_id | tag         |   
+-----------+-------------+
| 1         | x           |
| 1         | y           |
| 1         | z           |
+-----------+-------------+

+-------------+-------------+
| interest_id | tag         |   
+-------------+-------------+
| 10          | x           |
| 20          | y           |
| 20          | z           |
+-------------+-------------+

+-------------+-------------+
| skill_id    | tag         |   
+-------------+-------------+
| 100         | x           |
| 100         | y           |
| 100         | z           |
| 900         | a           |
+-------------+-------------+

我想编写一个查询，该查询将根据给定 person_id(例如 1)的相关性返回如下结果。请注意“a”没有出现在下面的结果中:

+-------------+-------------+-------------+
| id          | typ         | score       |
+-------------+-------------+-------------+
| 100         | skill       | 3           |
| 20          | interest    | 2           |
| 10          | interest    | 1           |
+-------------+-------------+-------------+

我怀疑 UNION 会是我的 friend ，但不太确定如何编写查询。有人有建议吗？

Answer 1

您可以将union all与group by表达式结合使用

select skill_id , 'skill' as typ, count( skill_id ) as score 
  from skill_tag 
 group by skill_id
union all
select interest_id , 'interest' as typ, count(interest_id) as score 
  from interest_tag 
 group by interest_id
 order by score desc

附注顺便说一句，您不需要 person_tag 表。