mysql - UNION ALL之后的WHERE子句

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

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

注意：“ seekers”表和下面现有查询中唯一的公共列和值是“ username”列。我的困惑来自于试图找出如何将下面的查询链接到“ seekers”表。

$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)
";

最佳答案

@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

关于mysql - UNION ALL之后的WHERE子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36224301/