mysql - UNION ALL 之后的 WHERE 子句

Question

我有一个名为“求职者”的表格，其中包含人员列表。有一个名为“username”的列，其中包含人员的用户名，还有一个名为“resume_status”的列，其值为 0 或 1。

目前下面的查询根本不检查“搜索者”表。我希望它只显示“resume_status”列中值为“1”的结果。

注意:“搜索者”表和下面现有查询中的唯一公共(public)列和值是“用户名”列。我的困惑来自于试图弄清楚如何将下面的查询链接到“搜索者”表。

$query="
    (SELECT username, MATCH(highlight) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score FROM resume_highlights HAVING score>0 ORDER by score desc)
    UNION ALL
    (SELECT username, MATCH(skill,skill_list) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score FROM resume_skills HAVING score >0 ORDER by score desc)
    UNION ALL
    (SELECT username, MATCH(education_title,education_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score FROM resume_education HAVING score >0 ORDER by score desc)
    UNION ALL
    (SELECT username, MATCH(employer_title,employer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score FROM resume_employer HAVING score>0 ORDER by score desc)
    UNION ALL
    (SELECT username, MATCH(volunteer_title,volunteer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score FROM resume_volunteer HAVING score >0 ORDER by score desc)
";

Answer 1

@Brijesh 的回答很好，但我认为这会更快——它还会通过在 seekers 表上添加一个索引来改进，我们希望在其他表上使用用户名索引。..

SELECT * 
FROM
(
  SELECT a.username, MATCH(a.highlight) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
  FROM resume_highlights a
  JOIN seekers ON a.username = seekers.username and seekers.resume_status = 1
  HAVING score>0 

    UNION ALL

  SELECT b.username, MATCH(b.skill,b.skill_list) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
  FROM resume_skills b
  JOIN seekers ON b.username = seekers.username and seekers.resume_status = 1
  HAVING score >0 

    UNION ALL

  SELECT c.username, MATCH(c.education_title,c.education_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
  FROM resume_education c
  JOIN seekers ON c.username = seekers.username and seekers.resume_status = 1
  HAVING score >0 

    UNION ALL

  SELECT d.username, MATCH(d.employer_title,d.employer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
  FROM resume_employer d
  JOIN seekers ON d.username = seekers.username and seekers.resume_status = 1
  HAVING score>0 

    UNION ALL

  SELECT e.username, MATCH(e.volunteer_title,e.volunteer_organization) AGAINST (\"{$keywords}\" IN BOOLEAN MODE) AS score 
  FROM resume_volunteer e
  JOIN seekers ON e.username = seekers.username and seekers.resume_status = 1
  HAVING score >0 

) AS X
ORDER BY SCORE desc