如何在MySQL中使用多个关键字对搜索结果进行分组

问题描述：

I have a database with keywords that are linked to a certain post::

Table word_index: word_id - keyword
Table word_rel: word_id - postId - unique_id


Example word_index:
1 - keyword1
2 - keyword2
3 - keyword3 
...

Example word_rel:
1 - 1 - 1
2 - 1 - 2
3 - 2 - 3
...

Now users may search for one or more keywords (just like Google works).

I use a SQL query, this works fine. However, when they use multiple keywords I want the results to be stricter and only show the results that have both keywords as a match. Now the query returns all posts that have one of the keywords as a match.

SELECT g.postName
FROM
(SELECT gr.postId FROM word_index wi INNER JOIN word_rel gr ON wi.word_id = 
gr.word_id (keyword1,keyword2) ) prr
INNER JOIN posts g ON g.postId = prr.postId
GROUP BY g.postId DESC

So the Select in the FROM (SELECT ...) part selects all matching items. After that it should only show all matches with both keywords and not one of the two.

Can I do this in the last GROUP BY part or do I have to change everything?

我有一个数据库，其关键字链接到某个帖子:: p> 表word_index：word_id - keyword Table word_rel：word_id - postId - unique_id 例如word_index： 1 - keyword1 2 - keyword2 3 - keyword3 ... 示例word_rel ： 1 - 1 - 1 2 - 1 - 2 3 - 2 - 3 ... code> pre>

现在用户可以搜索一个或多个关键字（就像谷歌一样）。 p>


我使用SQL查询，这很好用。 但是，当他们使用多个关键字时，我希望结果更严格，只显示两个关键字匹配的结果。 现在查询返回所有其中一个关键字匹配的帖子。 p> 
 
 
  SELECT g.postName 
FROM 
（SELECT gr.postId FROM word_index wi INNER JOIN word_rel gr  ON wi.word_id = 
gr.word_id（keyword1，keyword2））prr 
INNER JOIN post g ON g.postId = prr.postId 
GROUP BY g.postId DESC 
  code>  pre> 
 
  因此 FROM（SELECT ...） code>部分中的 Select  code>选择所有匹配的项目。 之后，它应该只显示两个关键字的所有匹配，而不是两个中的一个。 p> 
 
 
我可以在最后的 GROUP BY  code>部分执行此操作，还是我有 改变一切？ p> 
  div>

答

You can do this with GROUP BY and HAVING:

SELECT gr.postId
FROM word_index wi INNER JOIN
     word_rel gr
     ON wi.word_id = gr.word_id
WHERE wi.keyword IN (keyword1, keyword2)
GROUP BY gr.postId
HAVING COUNT(*) = 2;

You need to construct the IN list from the search string provided. You then need to assign the comparison value in the HAVING based on the number of keywords you want to match.

Note: You may need to do some pre-processing to be sure that keywords are not entered twice.

如何在MySQL中使用多个关键字对搜索结果进行分组

相关推荐