选择同时匹配多个值的所有数据(Select all the data matching multiple values in the same time)

选择同时匹配多个值的所有数据(Select all the data matching multiple values in the same time)

我的数据库中有以下2个表：

声明

和标签

当我需要选择匹配某个标记（ $tag ）的所有语句时，我使用这个SQL查询：

$select = "SELECT Statements.id, Statements.name, Statements.link FROM Statements JOIN Tags ON Statements.id = Tags.probID AND Tags.tag = '$tag'";

现在我需要在同一时间选择与几个标签匹配的所有语句。 我怎样才能做到这一点？ 我应该更改数据库中的内容吗？

I have 2 following tables in my DB:

Statements

and Tags

When I need to select all the statements matching some tag ($tag), I use this SQL query:

$select = "SELECT Statements.id, Statements.name, Statements.link FROM Statements JOIN Tags ON Statements.id = Tags.probID AND Tags.tag = '$tag'";

Now I need to select all the statements matching a few tags in the same time. How can I do that? Should I change something in my DB?

最满意答案

您可以使用IN匹配标记，然后使用GROUP BY确保您拥有所有标记：

SELECT s.id, s.name, s.link FROM Statements s JOIN Tags t ON s.id = t.probID WHERE t.tag IN ('$tag1', '$tag2', '$tag3') GROUP BY s.id, s.name, s.link HAVING COUNT(DISTINCT t.tag) = 3;

注意：上面的示例适用于三个标记。 HAVING子句中的数字需要与您要查找的标签数相匹配。

You can match the tags using IN and then use GROUP BY to ensure that you have all of them:

SELECT s.id, s.name, s.link FROM Statements s JOIN Tags t ON s.id = t.probID WHERE t.tag IN ('$tag1', '$tag2', '$tag3') GROUP BY s.id, s.name, s.link HAVING COUNT(DISTINCT t.tag) = 3;

Note: the above example is for three tags. The number in the HAVING clause needs to match the number of tags you are looking for.