复杂的 SQL 查询，多对多 [英] complex SQL query, many to many

问题描述

对我来说很复杂，因为我是 SQL 新手.

Complex for me as I am newbie in SQL.

我有三个表 - Peoples、Interests 和 Peoples_Interests(多对多)——它们以下列方式连接:

I have three tables - Peoples, Interests and Peoples_Interests (many-to-many) - that are connected in the following way:

People 通过 Peoples_Interests
有很多 InterestsInterest 通过 Peoples_Interests

我需要根据相似兴趣的数量向与他们最相似的人提出建议.所以对于示例:

I need to propose suggestions to Peoples with most similar to them Peoples, which is based on amount of similar Interests. So for example:

我对棒球、足球和排球感兴趣.我应该向另一个拥有尽可能多相似兴趣的用户寻求建议.出现 3/3 的人应该是我需要的，如果他们存在的话(如果不是 - 2/3 等等).

I am interested in baseball, football and volley. I should get suggestion with another user that have as many similar interests as possible. People with 3/3 occurrences should be what I need if they exist (if not - 2/3 and such).

所以我需要一个查询，输出将按兴趣相似度排序.

So I need a query that output will consist of sorted by interests similarity Peoples.

更新:数据库结构:

兴趣
身份证件
名称 - 字符串

Interests
id
name - string

人民
身份证件
电子邮件

Peoples
id
email

人民_兴趣
兴趣 ID
peoples_id

Peoples_Interests
interests_id
peoples_id

谢谢.

推荐答案

类似这样的事情.

Select people.id, people.name, count(interest.id)
from people
left join people_interests on people.id = people_interests.peopleid 
left join interests on people_interests.interestid = interests.interest.id
where interests.id in (select id from interests where interests.peopleid = @inputuserid)
group by people.id, people.name
order by count(interest.id)

英文(可能会也可能不会更清楚.)

In english (which may or may not make it clearer.)

• 选择此人的姓名和他们分享的兴趣数量
• 来自人员表
• 加入兴趣表，使得该表
• 只是我们试图匹配的人的兴趣.
• (按人分组
• 并按匹配的兴趣数量排序.)

更新没有子查询但不太清楚

Updated without the sub query but less clear

Select people.id, people.name, count(interest.id)
from people
left join people_interests on people.id = people_interests.peopleid 
left join interests on people_interests.interestid = interests.interest.id
inner join interest i2 on (interests.id = i2.id and i2.people_id = @inputuserid)
group by people.id, people.name
order by count(interest.id)

这篇关于复杂的 SQL 查询，多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！