根据类似于mysql的标签选择相关标题 [英] select relevance title based on tag similar to like with mysql

问题描述

标签

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

帖子

post_id  title
-------------------
1        some good title
2        some good
3        some
4        good title

我们如何获得在同一post_id中包含值 some和good 的post_id = 1和2?

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

所以结果是

RESULT
title
----------
some good title
some good

good title剂量显示，因为标签的post_id = 4中没有some值. 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%'

请参阅此SQLFiddle

您还可以像这样连接两个表:

See this SQLFiddle

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;

请参阅此SQLFiddle

注意:如果不使用HAVING子句，它还将返回存在任何单个值的记录

See this SQLFiddle

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

参见具有类似表结构的类似要求.

这篇关于根据类似于mysql的标签选择相关标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！