UNION ALL之后的WHERE子句

debugcn 发表于 Dev

我有一个名为“ 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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。