mysql - 将另一个表添加到 mySQL 查询

Question

到目前为止，我有以下运行良好的 SQL 查询:

SELECT *,
  MATCH(title, content_plain_text) AGAINST ('$searchTerm') AS score, 
  (MATCH(title, content_plain_text) AGAINST ('$searchTerm') / maxScore) AS normalisedScore
FROM pages,
  (SELECT MAX(MATCH(title, content_plain_text) AGAINST ('$searchTerm')) AS maxScore 
    FROM pages) maxScoreTable
    WHERE MATCH(title, content_plain_text) AGAINST ('$searchTerm')
    AND active = 1
    ORDER BY score DESC

所以现在我也想搜索另一个具有相同字段名称的表，如何在保持排名等的同时进行搜索？

谢谢!

Answer 1

我只是union all你的pages表和另一个表(假设它叫more_pages)。

WITH 结构确实能很好地解决这些问题，但不幸的是 MySQL 还不支持它。我能想到的最优雅的替代方法是创建一个 View 来封装这个联合:

CREATE VIEW all_pages AS
SELECT * FROM pages
UNION ALL
SELECT * FROM more_pages


SELECT *,
  MATCH(title, content_plain_text) AGAINST ('$searchTerm') AS score, 
  (MATCH(title, content_plain_text) AGAINST ('$searchTerm') / maxScore) AS normalisedScore
FROM all_pages,
  (SELECT MAX(MATCH(title, content_plain_text) AGAINST ('$searchTerm')) AS maxScore 
    FROM all_pages) maxScoreTable
    WHERE MATCH(title, content_plain_text) AGAINST ('$searchTerm')
    AND active = 1
    ORDER BY score DESC