mysql:联合所有并按相关性排序？

Question

SELECT id, title, keywords, post, img, 
       NULL as device_id, null as mfgr, null as model, null as img 
       FROM blog_posts WHERE MATCH(title, desc_meta, keywords_meta, post) AGAINST('boost')
       UNION ALL
       SELECT NULL, NULL, NULL, NULL, id AS device_id, mfgr, model, img, summary
       FROM device_specs WHERE MATCH(mfgr, model, summary) AGAINST('boost');

考虑上面的查询，其中有两个没有任何共同点的表。这仅用于搜索脚本的目的。我将如何保持与两个组合表的相关性？上面的查询有效，但在所有情况下，blog_posts 的结果都会出现在 device_specs 的结果之前。

Answer 1

您想要添加与每个子查询的相关性。您可以在不使用子查询的情况下执行此操作:

SELECT id, title, keywords, post, img, 
       NULL as device_id, null as mfgr, null as model, null as img,
       MATCH(title, desc_meta, keywords_meta, post) AGAINST('boost') as relevance
FROM blog_posts
WHERE MATCH(title, desc_meta, keywords_meta, post) AGAINST('boost') > 0
UNION ALL
SELECT NULL, NULL, NULL, NULL, id AS device_id, mfgr, model, img, summary,
       MATCH(mfgr, model, summary) AGAINST('boost') as relevance
FROM device_specs
WHERE MATCH(mfgr, model, summary) AGAINST('boost') > 0
ORDER BY relevance;