我如何改进这个查询？

Question

您可能想看看我以前的问题。

我的数据库模式如下所示

         ---------------                              ---------------   
         | candidate 1 |                              | candidate 2 |
         --------------- \                             --------------      
           /              \                                 |
       -------              --------                        etc
       |job 1|              | job 2 |  
       -------              ---------  
        /     \              /      \  
  ---------   ---------  ---------   --------  
  |company |  | skills | |company | | skills |  
  ---------   ---------  ---------- ----------  

这是我的数据库：

mysql> describe jobs;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| job_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| candidate_id | int(11) | NO   | MUL | NULL    |                |
| company_id   | int(11) | NO   | MUL | NULL    |                |
| start_date   | date    | NO   | MUL | NULL    |                |
| end_date     | date    | NO   | MUL | NULL    |                |
+--------------+---------+------+-----+---------+----------------+

。

mysql> describe candidates;
+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| candidate_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| candidate_name | char(50) | NO   | MUL | NULL    |                |
| home_city      | char(50) | NO   | MUL | NULL    |                |
+----------------+----------+------+-----+---------+----------------+

。

mysql> describe companies;
+-------------------+---------------+------+-----+---------+----------------+

| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| company_id        | int(11)       | NO   | PRI | NULL    | auto_increment |
| company_name      | char(50)      | NO   | MUL | NULL    |                |
| company_city      | char(50)      | NO   | MUL | NULL    |                |
| company_post_code | char(50)      | NO   |     | NULL    |                |
| latitude          | decimal(11,8) | NO   |     | NULL    |                |
| longitude         | decimal(11,8) | NO   |     | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+

。

请注意，我可能应该将此称为skill_usage，因为它指示何时使用技能，don作业。

mysql> describe skills;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

。

mysql> describe skill_names;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| skill_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| skill_name | char(32) | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

到目前为止，我的MySQL查询如下所示：

SELECT DISTINCT can.candidate_id, 
                can.candidate_name, 
                     can.candidate_city,        
                     j.job_id, 
                     j.company_id,
                DATE_FORMAT(j.start_date, "%b %Y")  AS start_date, 
                DATE_FORMAT(j.end_date, "%b %Y") AS end_date,        
                s.skill_id  
FROM  candidates AS can       
  INNER JOIN jobs AS j ON j.candidate_id = can.candidate_id     
  INNER JOIN companies AS co ON j.company_id = co.company_id        
         INNER JOIN skills AS s ON s.job_id = j.job_id 
            INNER JOIN skill_names AS sn ON s.skill_id = s.skill_id 
   AND sn.skill_id = s.skill_id 
ORDER by can.candidate_id, j.job_id

我得到了这样的产出，但对此不满意。

   +--------------+----------------+---------------------+--------+------------+------------+------------+----------+
   | candidate_id | candidate_name | candidate_city      | job_id | company_id | start_date | end_date   | skill_id |
   +--------------+----------------+---------------------+--------+------------+------------+------------+----------+
   |            1 | Pamela Brown   | Cardiff             |      1 |          3 | 2019-01-01 | 2019-08-31 |        1 |
   |            1 | Pamela Brown   | Cardiff             |      1 |          3 | 2019-01-01 | 2019-08-31 |        2 |
   |            1 | Pamela Brown   | Cardiff             |      1 |          3 | 2019-01-01 | 2019-08-31 |        1 |
   |            1 | Pamela Brown   | Cardiff             |      2 |          2 | 2018-06-01 | 2019-01-31 |        3 |
   |            1 | Pamela Brown   | Cardiff             |      3 |          1 | 2017-11-01 | 2018-06-30 |        4 |
   |            1 | Pamela Brown   | Cardiff             |      3 |          1 | 2017-11-01 | 2018-06-30 |        5 |
   |            1 | Pamela Brown   | Cardiff             |      3 |          1 | 2017-11-01 | 2018-06-30 |        6 |
   |            1 | Pamela Brown   | Cardiff             |      4 |          3 | 2016-08-01 | 2017-11-30 |        1 |
   |            2 | Christine Hill | Salisbury           |      5 |          2 | 2018-02-01 | 2019-05-31 |        3 |

现在，我想通过指定“技能”来限制搜索，比如Python、C、C++、UML等以及公司名称。

用户将向技能搜索框中输入类似Python AND C++的内容(并/或将Microsoft OR Google输入公司名称搜索框)。

如何将其输入到查询中？请记住，每个技能ID都有一个与其相关的职务Id。也许我首先需要将搜索中的技能名称(在本例中是Python C++)转换为技能Ids？即便如此，我如何在查询中包括这些内容呢？

让一些事情变得更清楚：

• “技能与公司搜索”框都可以是空的，我将将其解释为“返回所有内容”。
• 搜索条件可以包括关键字和OR，加上分组括号(不需要)。我很高兴在PHP中解析它&把它变成一个MySQL查询术语(我的困难仅仅是使用SQL，而不是PHP)。

看起来我开始了，用那个INNER JOIN skills AS s ON s.job_id = j.job_id，我认为它将处理一个单一技能的搜索，鉴于它的.名字？身份证？

我想我的问题是，如果我想把结果限制在任何在Microsoft OR Google工作过并且有Python AND C++技能的人身上，那么这个查询会是怎样的呢？

如果我得到一个例子，我可以推断，但在这一点上，我不确定我是否想要更多的内部联接或哪里的子句。

我想我想通过解析技能搜索字符串来扩展第二行AND sn.skill_id = s.skill_id，在我的示例Python AND C++中，生成一些类似于AND (s.skill_id = X )的SQL，其中X是Python的技能Id，但是--我不知道如何处理Python AND C++，或者更复杂的东西，比如Python AND (C OR C++) .

更新

为了明确起见，用户是技术性的，希望能够进入复杂的搜索。技能：(C AND kernel)OR (C++ AND realtime) OR (Doors AND (UML OR QT))。

最后更新

要求刚变了。我正在编写这篇文章的人告诉我，如果一个候选人与他曾经工作过的任何工作的技能搜索相匹配，那么我应该为该候选人返回所有的职位。

这听起来违反我的直觉，但他发誓这是他想要的。我甚至不确定它是否可以在一个查询中完成(我正在考虑多个查询，首先不能获得具有匹配技能的候选人，然后是获得所有工作的第二次查询)。

Answer 1

我要说的第一件事是，您最初的查询可能需要在技能表上添加一个外部连接--就目前情况而言，它只检索其工作具有技能的人员(可能不是所有的任务)。您说“技能和公司搜索框都可以是空的，我将将其解释为返回所有内容”--这个版本的查询不会返回所有内容。

其次，我将您的“技能”表重命名为"job_skills"，将您的"skill_names“重命名为”技能“--它更一致(您的公司表不称为company_names)。

您显示的查询有一个复制-- AND sn.skill_id = s.skill_id重复了连接的条件。这是故意的吗？

要回答您的问题:我将在PHP中的某种预定义列表中向您的用户展示与skill_id相关的技能。您可以使用复选框列出所有技能，或者允许用户开始键入并使用AJAX搜索匹配文本的技能。这解决了UI问题(如果用户试图搜索不存在的技能怎么办？)，并使SQL稍微容易一些。

然后，您的查询变成：

SELECT DISTINCT can.candidate_id, 
                can.candidate_name, 
                can.candidate_city,        
                j.job_id, 
                j.company_id,
                DATE_FORMAT(j.start_date, "%b %Y")  AS start_date, 
                DATE_FORMAT(j.end_date, "%b %Y") AS end_date,        
                s.skill_id  
FROM  candidates AS can       
  INNER JOIN jobs AS j ON j.candidate_id = can.candidate_id     
  INNER JOIN companies AS co ON j.company_id = co.company_id        
  INNER JOIN skills AS s ON s.job_id = j.job_id 
  INNER JOIN skill_names AS sn ON s.skill_id = s.skill_id 
AND skill_id in (?, ?, ?)
OR skill_id in (?)
ORDER by can.candidate_id, j.job_id

您需要用问号代替用户输入的内容。编辑

允许用户以免费文本的形式输入技能的问题是，您必须处理大小写转换、空格和排字。例如，"Python“是否与"python”相同？您的用户可能打算这样做，但您不能与skill_name进行简单的比较。如果要允许自由文本，一种解决方案可能是添加一个“规范化”skill_name列，在该列中以一致格式存储名称(例如，“所有大写，去掉空白”)，并以相同的方式规范输入值，然后与该规范化列进行比较。在这种情况下，"in子句“变成了类似于：

AND skill_id in (select skill_id from skill_name where skill_name_normalized in (?, ?, ?))

您提到的布尔逻辑- (C或C++)和(敏捷)-变得非常棘手。最后，您将编写一个“可视化查询生成器”。你可能想谷歌这个学期-有一些好例子。

你在一定程度上缩小了你的要求(我可能误解了)。我相信你的要求是

我希望能够指定零或多个过滤器。 过滤器由一个或多个ANDed技能组组成。 技能组由一项或多项技能组成。 过滤器是ORed一起创建查询。

为了使这个具体，让我们使用您的例子- (A and (B OR C)) OR (D AND (E OR F))。有两个过滤器：(A and (B OR C))和(D AND (E OR F))。第一个过滤器有两个技能组：A和(B OR C)。

很难用文本来解释这个建议，但是您可以创建一个UI，允许用户指定单独的“筛选器”。每个“筛选器”将允许用户指定一个或多个"in子句“，并与"and”连接。然后您可以将其转换为SQL --同样，使用您的示例，SQL查询将变为

SELECT DISTINCT can.candidate_id, 
                can.candidate_name, 
                can.candidate_city,        
                j.job_id, 
                j.company_id,
                DATE_FORMAT(j.start_date, "%b %Y")  AS start_date, 
                DATE_FORMAT(j.end_date, "%b %Y") AS end_date,        
                s.skill_id  
FROM  candidates AS can       
  INNER JOIN jobs AS j ON j.candidate_id = can.candidate_id     
  INNER JOIN companies AS co ON j.company_id = co.company_id        
  INNER JOIN skills AS s ON s.job_id = j.job_id 
  INNER JOIN skill_names AS sn ON s.skill_id = s.skill_id 
AND 
  (skill_id in (A) and skil_id in (B, C))
OR 
  (skill_id in (D) and skil_id in (E, F))
ORDER by can.candidate_id, j.job_id

Answer 2

从以前的评论和回答中.如果像这样处理输入

(A and (B OR C)) OR (D AND (E OR F))是您可以尝试将一些条件逻辑移出联接和筛选器的阻止程序。

WHERE (
          ((sn.skill_id LIKE 'A') AND ((sn.skill_id LIKE ('B')) OR (sn.skill_id LIKE('C')))) 
       AND ((co.company_id IN (1,2,3)) AND ((can.city = 'Springfield') OR (j.city LIKE('Mordor'))))
     )

您可以根据使用的输入构建查询字符串，搜索Id以查找选定的值，并将它们放入字符串中，并有条件地构建任意数量的筛选器。考虑设置add_and_filter和add_or_filter函数来构造<db>.<field> <CONDITION> <VALUE>语句。

$qs = "";
$qs .= "select val from table";
...
$qs .= " WHERE ";
if($userinput){ $qs += add_and_filter($userinput); }

或者，看一个map/还原模式，而不是试着用SQL来完成它？