跨多个相关表的相关性搜索
跨多个相关表的相关性搜索
提问于 2012-03-31 15:23:04
我有一个名为cards的表，它有相关的表brigades和identifiers。一张卡可以有多个大队和标识符。我想进行一个单独的搜索，比如'purple king'，其中‘紫色’是一个旅，‘国王’是一个标识符，并找到具有这些旅和标识符的牌。对一个类似问题的回答，https://stackoverflow.com/a/9951200/633513，展示了如何跨多个表进行全文搜索。我也想做同样的事情，除了我只想要相关的匹配。这个是可能的吗？
表结构：
Cards: id as INT, title as VARCHAR(50)
Brigades: id as INT, brigade as VARCHAR(50)
Identifier: id as INT, identifier as VARCHAR(50)连接表：
CardBrigades: id as INT, card_id as INT, brigade_id as INT
CardIdentifiers: id as INT, card_id as INT, identifier_id as INT示例标识符：
Angel
Animal
Archer
Army
Assyrian
Babylonian
Based on Prophecy
Beast
Bows, Arrows, Spears, Javelins and Darts
Canaanite
'Capture' in Special Ability
'Censer' in Title
Chief Priest
Child of Leah
Commander
Connected to David
Connected to Demons
'Covenant' in Title
'David' in Title
'David' in Verse
Deacon
Prince示例旅：
None
Black
Brown
Crimson
Gold (Evil)
Gray
Orange
Pale Green
Multi (Evil)
Blue
Gold (Good)
Green
Purple
Red
Silver
Teal
White
Multi (Good)
Multi回答 2
Stack Overflow用户
回答已采纳
发布于 2012-03-31 16:46:01
根据你发布的链接中的答案，你可以这样做
SELECT id,SUM(relevance) as total_relevance FROM (
SELECT
id,
(MATCH(title) AGAINST ('search string')) AS relevance
FROM Cards
UNION
SELECT
Cards.id,
(MATCH(brigade) AGAINST ('search string')) AS relevance
FROM Brigades
INNER JOIN CardBrigades ON Brigades.id=brigade_id
INNER JOIN Cards ON card_id=Cards.id
UNION
SELECT
Cards.id,
(MATCH(identifier) AGAINST ('search string')) AS relevance
FROM Identifier
INNER JOIN CardIdentifier ON Identifier.id=identifier_id
INNER JOIN Cards on card_id=Cards.id
) AS combined_search
GROUP BY id
HAVING total_relevance > 0我不确定这会有多好。你最好看看其他的解决方案，比如Solr，Lucene，甚至NoSQL存储引擎。
Stack Overflow用户
发布于 2013-10-12 15:53:19
如果你只想要相关的(相关的？)结果您可以在布尔模式下使用全文搜索，如下所示：
select
identifier, brigade, P.id as identifier_id, B.id as brigade_id,
match(identifier) against ('purple prince') +
match(brigade) against ('purple prince') as score
from Identifier P, Brigade B
where
match(identifier) against ('purple prince' IN BOOLEAN MODE) and
match(brigade) against ('purple prince' IN BOOLEAN MODE)
order by -score
;(为了简单起见，我只显示了查询的全文搜索部分，并省略了与Card*表的连接)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9953922
复制相关文章
相似问题
腾讯云开发者
