我正在索引帖子的两件事。 一个是标题(单词),两个是关键字。 我选择使用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 titleNow 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.postIdBut 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.postIdThe 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 ASCYou 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.postIdAnd 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
更多推荐
发布评论