如何从表中提取一个唯一字段和其他字段的特定值的所有行？ [英] How do I pull all rows from a table with one unique field and specific values for another field?

问题描述

这是我要完成的工作：

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

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.

例如，我想从具有skill_id 110的users_skills 120和130.如果users_skills中的用户没有全部3个，我不想在我的结果中。

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.

我试过一个简单的AND（like users_skills.skill_id = 110 AND users_skills.skill_id = 120 AND users_skills.skill_id = 130 AND user_skills.skill_id = 130）but that does not work。

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.

我该如何做到这一点？谢谢！

How would I accomplish this? Thanks!

推荐答案

尝试使用自我加入：

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

需要来自表 users 和 skills 的数据，在这种情况下，您可以使用

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

这篇关于如何从表中提取一个唯一字段和其他字段的特定值的所有行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！