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

Question

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?

Answer 1

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.