sql - 复杂的SQL语句: select that matches on multiple tables

Question

我有以下表格:

Candidates

CandidateLanguages

CandidateSkills

每个候选人可能拥有超过 1 种语言和超过 1 项技能

所以对于 Candidate “FRED”，他的记录在 CandidateLanguages或许

FRED - ENGLISH
FRED - FRENCH

以及他在 CandidateSkills 中的记录或许

FRED - RUNNING
FRED - JUMPING

和 Candidate “JIM”他的记录 CandidateLanguages或许

JIM - ENGLISH

以及他在 CandidateSkills 中的记录或许

JIM - RUNNING

我的查询需要选择匹配多种技能和语言的候选人。

所以例如在英语中:

选择所有会说所有选定语言并拥有所有选定技能的候选人...

或者换一种方式......

SELECT ALL candidates WHERE 
(language = 'FRENCH' AND language is 'ENGLISH') AND 
(skill = 'RUNNING' AND skill = 'JUMPING') 

在上面的两个候选人中，这应该只返回“FRED”

我知道问题在于尝试从语言和技能表中选择多个记录，我认为可能需要连接，但现在我迷路了......

Answer 1

您正在解决的问题名为 关系科 .

看这篇文章: Divided We Stand: The SQL of Relational Division 以及这个问题的几种解决方法: How to filter SQL results in a has-many-through relation

解决它的一种方法(通常是最有效的):

SELECT ALL c.candidate
FROM Candidates c
  JOIN CandidateLanguages lang1
    ON  lang1.candidate = c.candidate 
    AND lang1.language = 'English'
  JOIN CandidateLanguages lang2
    ON  lang2.candidate = c.candidate 
    AND lang2.language = 'French'
  JOIN CandidateSkills sk1
    ON  sk1.candidate = candidate 
    AND sk1.skill = 'Running'
  JOIN CandidateSkills sk2
    ON  sk2.candidate = candidate 
    AND sk2.skill = 'Jumping' ;

另一种看起来更容易编写的方法，尤其是在涉及很多语言和技能的情况下，是使用带有 GROUP BY 的两个派生表。在他们每个人中:

SELECT ALL c.candidate
FROM Candidates c
  JOIN 
    ( SELECT candidate
      FROM CandidateLanguages
      WHERE language IN ('English', 'French')
      GROUP BY candidate
      HAVING COUNT(*) = 2                     -- the number of languages
    ) AS lang
      ON  lang.candidate = c.candidate 
  JOIN 
    ( SELECT candidate
      FROM CandidateSkills 
      WHERE skill IN ('Running', 'Jumping')
      GROUP BY candidate
      HAVING COUNT(*) = 2                     -- the number of skills
    ) AS sk
      ON  sk.candidate = c.candidate ;