mysql - 选择具有几个相同参数的行

Question

我有 3 个表:Student(id, username)、Skill(id, value) 和 StudentSkill(id, student_id, skill_id).
在 Django 模型中，它们看起来像那样(短版):

class Student(models.Model):
    username = models.CharField(max_length = 50, unique = True)

class Skill(models.Model):
    value = models.CharField(max_length = 50, unique = True)

class StudentSkill(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    skill = models.ForeignKey(Skill, on_delete=models.CASCADE)

我需要编写一个请求，返回具有特定技能的学生的查询集。

例如，我在数据库中有以下数据:

Student
id     | username
------ | ------
1      | user1
2      | user2
3      | user3

Skill
id     | value
------ | ------
1      | s1
2      | s2
3      | s3

StudentSkill
id     | student_id | skill_id
------ | ---------- | --------
1      | 1          | 1
2      | 2          | 1
3      | 2          | 2
4      | 3          | 1
5      | 3          | 2
6      | 3          | 3

user1 有 1 项技能:(s1)
user2 有 2 个技能:(s1 和 s2)
user3 有 3 个技能:(s1, s2 and s3)

如果我需要带s1和s2的学生，我必须带user2和user3< br/>如果s1 --> user1, user2 and user3
如果s2 --> user2 和user3
如果s1和s3 --> user3

我知道有两种方法可以解决这个问题，但我认为它们都很糟糕。

首先:

students = Student.objects.raw('''
    SELECT t1.* FROM (
        SELECT student.* FROM student
            INNER JOIN studentskill ON studentskill.student_id = student.user_id
            INNER JOIN skill ON skill.id = studentskill.skill_id
            WHERE skill.id = 1
        ) AS t1
        INNER JOIN studentskill ON studentskill.student_id = t1.user_id
        INNER JOIN skill ON skill.id = studentskill.skill_id
        WHERE skill.id = 2
    ''')  

其次(我没有检查这段代码，它可能有错误):

students = Student.objects.filter(studentskill__skill__id = 1
    ).select_related('studentskill', 'skill'
    ).only('student', 'studentskill', 'skill__id' )
students.filter(studentskill__skill__id = 2)

Answer 1

你可以使用 group by 并且具有不同的技能 = 2

    select SELECT student.id, student.username
    FROM student
    INNER JOIN studentskill ON studentskill.student_id = student.user_id
    INNER JOIN skill ON skill.id = studentskill.skill_id
    WHERE skill.id in (1,2) 
    group by student.id, student.username
    havinge count(distinct studentskill.skill_id)= 2