如何从具有一个唯一字段的表中提取所有行，为另一个字段提取特定值？

Here's what I'm looking to accomplish:

这是我想要完成的事情:

I have a table called "users_skills". The table consists of two fields, "user_id" and "skill_id". I want to pull all rows from the table that show a user has specific skills.

我有一个名为“users_skills”的表。该表由两个字段“user_id”和“skill_id”组成。我想从表中提取显示用户具有特定技能的所有行。

For example, I want to pull all users from "users_skills" who have skill_id 110, 120, and 130. If a user in "users_skills" doesn't have all 3, I don't want them in my results.

例如,我想从拥有skill_id 110,120和130的“users_skills”中提取所有用户。如果“users_skills”中的用户没有全部3,我在结果中不需要它们。

I've tried a simple AND (like users_skills.skill_id=110 AND users_skills.skill_id=120 AND users_skills.skill_id=130) but that didn't work.

我尝试过一个简单的AND(比如users_skills.skill_id = 110 AND users_skills.skill_id = 120 AND users_skills.skill_id = 130),但是没有用。

How would I accomplish this? Thanks!

我怎么做到这一点?谢谢!

1 个解决方案

#1

Try using a self-join:

尝试使用自联接:

SELECT us1.* FROM users_skills us1
  JOIN users_skills us2 ON us1.user_id = us2.user_id
  JOIN users_skills us3 ON us1.user_id = us3.user_id
    WHERE us1.skill_id = 110 AND us2.skill_id = 120 AND us3.skill_id = 130

I assume you actually want the data from the tables users and skills, in that case, you could use

我假设您实际上想要表格中的数据用户和技能,在这种情况下,您可以使用

SELECT users.*, skills.* FROM users
  JOIN users_skills us1 ON users.id = user_id
  JOIN users_skills us2 ON users.id = us2.user_id
  JOIN users_skills us3 ON users.id = us3.user_id
  JOIN skills ON skills.id = us1.skill_id OR skills.id = us2.skill_id OR skills.id = us3.skill_id
    WHERE us1.skill_id = 110 AND us2.skill_id = 120 AND us3.skill_id = 130

#1