开发mysql类似,根据类似于mysql的标签选择相关标题

TAGS

tag_id post_id value

------------------------

1 1 some

2 1 good

3 1 title

4 2 some

5 2 good

6 3 some

7 4 good

8 4 title

POSTS

post_id title

-------------------

1 some good title

2 some good

3 some

4 good title

how can we get the post_id = 1 and 2 that contains value some and good in the same post_id?

so the result is

RESULT

title

----------

some good title

some good

good title dosent show becouse there is no some value in post_id = 4 in tags.

some doesnt show beouse the requirement good

解决方案

Try LIKE multiple time:

SELECT * FROM post

WHERE title LIKE '%some%'

AND title LIKE '%good%'

You can also join both tables like this:

SELECT post.post_id, title FROM Post

RIGHT JOIN Tags

ON post.post_id = tags.post_id

WHERE Tags.value IN ('some','good')

GROUP BY post.Post_ID

HAVING COUNT(*)>1;

Note: If we don't use HAVING clause, It will also return records where any single value exists