SQL索引/关系(SQL index/relation)

SQL索引/关系(SQL index/relation)

我正在索引帖子的两件事。 一个是标题（单词），两个是关键字。 我选择使用3个表格：

word_index (word,word_id) // al words and keywords are indexed keyword_rel (word_id,postId) // relations with the keywords word_rel (word_id,postId) // relations with the the words of the title

现在我正在尝试对此执行一个不错的搜索查询。 仅使用关键字，它工作正常。

  SELECT p.postId
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id=kr.word_id
  INNER JOIN post p ON p.postId=kr.postId       
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
 但现在我想包括word_rel以搜索标题。 
 
 我试过这个，但它听起来不对，也没有返回所有内容（缺少一些wi.word）： 
 
  SELECT p.postId,wi.word
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id=kr.word_id

  INNER JOIN word_rel wr ON wi.word_id = wr.word_id

  INNER JOIN post p ON p.postId=kr.postId 

  INNER JOIN post pi ON pi.postId=wr.postId 

  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
 问题在于INNER JOIN post p与keyword_rel有关。 现在我还需要它与word_rel有关。 这样做的好方法是什么？ 
I'm indexing two things of posts. One is the title (words) and two are the keywords. I've chosen to use 3 tables for this:
 
word_index (word,word_id) // al words and keywords are indexed
keyword_rel (word_id,postId) // relations with the keywords
word_rel (word_id,postId) // relations with the the words of the title
 
Now I'm trying to perform a decent search query on this. Using the keywords only, it works fine.
 
  SELECT p.postId
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id=kr.word_id
  INNER JOIN post p ON p.postId=kr.postId       
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
But now I would like to include word_rel to also search in the title. 
 
I tried this, but it doesn't sound right and also doesn't return everything (missing some of wi.word):
 
  SELECT p.postId,wi.word
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id=kr.word_id

  INNER JOIN word_rel wr ON wi.word_id = wr.word_id

  INNER JOIN post p ON p.postId=kr.postId 

  INNER JOIN post pi ON pi.postId=wr.postId 

  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
The problem is with INNER JOIN post p which is related to keyword_rel. Now I also need it related to word_rel. What is a good way to do this ?
                
最满意答案
                
                    
                        
 你需要在这里做一个UNION： 
 
  SELECT p.postId, wi.word
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id = kr.word_id
  INNER JOIN post p ON p.postId=kr.postId 
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId

  UNION

  SELECT p.postId, wi.word
  FROM word_index wi
  INNER JOIN word_rel wr ON wi.word_id = wr.word_id
  INNER JOIN post p ON p.postId=wr.postId
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
 并且因为这可能会将某些结果返回两次或更多次（特别是如果单词包含在关键字和标题中），您可能希望将上述查询包装到另一个SELECT DISTINCT ： 
 
SELECT DISTINCT t.post_id, t.word
FROM (
    SELECT ...
    UNION
    SELECT
) t
ORDER BY t.word ASC
You would need to do a UNION here:
 
  SELECT p.postId, wi.word
  FROM word_index wi
  INNER JOIN keyword_rel kr ON wi.word_id = kr.word_id
  INNER JOIN post p ON p.postId=kr.postId 
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId

  UNION

  SELECT p.postId, wi.word
  FROM word_index wi
  INNER JOIN word_rel wr ON wi.word_id = wr.word_id
  INNER JOIN post p ON p.postId=wr.postId
  WHERE wi.word LIKE 'input%'
  GROUP BY p.postId
 
And since this may return some of the results twice or more times (especially if the word is contained in both keywords and titles), You may want to wrap the above query into another SELECT DISTINCT:
 
SELECT DISTINCT t.post_id, t.word
FROM (
    SELECT ...
    UNION
    SELECT
) t
ORDER BY t.word ASC