我的数据库中有以下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.
更多推荐
发布评论