gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 16:06:06 24 4
gpt4 key购买 nike

我有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 ,但不太确定如何编写查询。有人有建议吗?

最佳答案

您可以将union allgroup 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 表。

关于mysql - SQL 进行简单的相关性排名,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55733636/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com