许多表SQL选择问题

I have two tables that are related, which, for the purpose of this question, will be called posts and tags. posts contains various postings, such as those found on a community forum system. tags contains a unique set of tags, that are added when the server encounters a new tag that is not already in the system. There is only one entry per tag.

我有两个相关的表，在这个问题中，它们将被称为posts和tags。贴子包含各种各样的贴子，比如在社区论坛系统中找到的贴子。标记包含一组惟一的标记，当服务器遇到系统中还没有的新标记时，将添加这些标记。每个标签只有一个条目。

posts can have multiple tags, and tags can have more than one post that is a reference to it. To handle these references back and forth, I have created a table to sit in between these two tables, called posttags. posttags contains a reference to the tag id and the post id. This is how the many to many relationship is maintained.

贴子可以有多个标签，标签可以有一个以上的贴子作为它的引用。为了来回处理这些引用，我创建了一个表，放在这两个表之间，名为posttags。posttags包含对标记id和post id的引用。

Now, on to the problem. I need to be able to select posts based on a tag. This is a simple join when there is only one tag to search for, but I am at a loss as to how to handle multiple tags. For instance, I need to be able to search the database and get results that have ALL of the tags that are in a list (e.g., "php, mysql, sql"), without using SQL inside of a loop or any other low performance options.

现在，我们来看看这个问题。我需要能够根据标签选择文章。这是一个简单的连接，当只有一个标记来搜索时，但是对于如何处理多个标记，我感到不知所措。例如，我需要能够搜索数据库并得到具有列表中所有标记(例如，“php、mysql、sql”)的结果，而不需要在循环中使用sql或任何其他低性能选项。

I am not sure how to do this. Can anyone point me in the correct direction?

我不知道该怎么做。谁能指点我正确的方向吗?

Thanks!

谢谢!

2 个解决方案

#1

You can use the following approach, 3 is quantity of requested tags.

您可以使用以下方法，3是请求标记的数量。

SELECT PostId
FROM posttags pt, tags t
WHERE p.Id = pt.PostId
  AND t.Id = pt.TagId
  AND tagname In ('php', 'mysql', 'sql')
group by PostId
having count(*) = 3

or you can sort you requested tags join them in order and use the following:

或者你可以将你所请求的标签按顺序排列，加入它们，并使用以下内容:

Select * from Posts p,
(  
 SELECT PostId, GROUP_CONCAT(tagname ORDER BY tag_name DESC SEPARATOR ' ') Tags
 FROM posttags pt, tags t
 WHERE p.Id = pt.PostId
   AND t.Id = pt.TagId
 GROUP BY PostId

) t WHERE t.PostId = p.Id AND t.Tags = 'mysql php sql'

)t,t。PostId = p。Id和t。标签= 'mysql php sql'

But you need sort your tags

但是你需要对标签进行排序

#2

If you want to select the posts which have all tags in a given set, you can use a "counting" strategy:

如果你想在给定的集合中选择所有标签的文章，你可以使用“计数”策略:

SELECT
  p.*
FROM tags t
LEFT JOIN posttags pt
  ON pt.tag_id = t.tag_id
LEFT JOIN posts p
  ON p.post_id = pt.post_id
WHERE
  tags.tag IN ('php', 'mysql', 'sql')
GROUP BY p.post_id
HAVING COUNT(tags.tag_id) = 3;

Of course, if you change the set, the number "3" should be changed to the number of elements in the set.

当然，如果您更改集合，那么数字“3”应该更改为集合中元素的数量。

#1