有点复杂的MySql查询 [英] Somewhat complex MySql query

问题描述

下面的示意图.基本上，最底层是技能，然后是工作，然后是应聘者(暂时忽略公司).每个技能可以与多个职位相关联，并且一个职位可以具有多个技能.每个职位都适用于一个候选人，该候选人可以拥有(拥有)多个(历史性)职位，每个职位都由一个公司负责.

Schema diagram below. Basically, at the bottom are skills, then jobs, then candidates (ignore the companies for now). Each skill can be associated with multiple jobs, and a job can have multiple skills. Each job applies to a single candidate who can have (had) multiple (historical) jobs, each with a single company.

我只是无法弄清楚要找到所有技能X的候选人的问题.

I just can't figure out the queries to find all candidates with skill X.

实际上，它变得复杂，因为X不仅是单个技能，还可以是多个技能，并且具有布尔运算符，例如

In fact, it gets complicated, because X isn't just a single skill, it can be multiple skills, with Boolean operators, such as

使用(skill ="C ++"和skill ="UML")和NOT(skill ="Python")查找所有候选人

find all candidates with (skill="C++" and skill="UML") and NOT(skill="Python")

其中(skill="C++" and skill="UM"L) and NOT(skill="Python")部分是一个字符串，应该包含一个有效的SQl子查询，但是我无法弄清楚查询的其余部分.

where the (skill="C++" and skill="UM"L) and NOT(skill="Python") part is a string which ought to contain a valid ,SQl sub-query, but I can't figure the rest of the query.

[更新]当我说如"时，我并不是指该查询字符串.我试图找到一种方法来处理任何查询字符串的技能.例如skill=VB或skill=VB and skill=C或skillFreeRTOS and not skill=Windows

[Update] when I said "such as", I did not mean exactly that query string. I am trying to find a way handle any query string of skills. Eg skill=VB or skill=VB and skill=C or skillFreeRTOS and not skill=Windows

顺便说一句，该模式来自这个问题的答案对我来说看起来不错，但是...

Btw, the schema came form the answer to this question It looks good to me, but ...

推荐答案

使用(skill ="C ++"和skill ="UML")和NOT(skill ="Python")查找所有候选人

find all candidates with (skill="C++" and skill="UML") and NOT(skill="Python")

我会推荐group by和having.

select j.candidate_id
from jobs j join
     skills s
     on j.job_id = s.job_id join
     skill_names sn
     on sn.skill_id = s.skill_id
group by j.candidate_id
having sum(s.skill_name = 'C++') > 0 and
       sum(s.skill_name = 'UML') > 0 and
       sum(s.skill_name = 'Python') = 0;

需要注意的是，这使技能与职位相关，而不是直接与求职者相关.也许这是您定义技能的必要条件，但很可能您根本就没有某些应聘者的某些技能的工作.

The caveat is that this gets the skills attached to jobs, not directly to the candidate. Perhaps that it is a requirement for your definition of skill, but it is possible that you simply have no jobs for some skills for some candidates.

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