MySQL：返回匹配所有标签的对象的计数

发布于 2024-11-05 06:51:38 字数 541 浏览 0 评论 0原文

我遇到一种情况，我需要同时将一个对象与多个标签匹配，以便结果集“缩小”以匹配所有标签。我找到了以下 MySQL 查询：

  SELECT * 
    FROM OBJECTS o
    JOIN OBJECTSTAGS ot ON ot.object_id = o.id
    JOIN TAGS t ON t.id = ot.tag_id
   WHERE t.name IN ('tag1','tag2')
GROUP BY o.id
  HAVING COUNT(DISTINCT t.name) = 2

... 其中 2 是匹配的标签数。效果很好。

但是，我需要查询返回对象的计数而不是对象本身。如果我将 COUNT(*) 添加到 SELECT 中，此查询似乎会感到困惑。例如，我不太愿意只返回 ids 并对它们进行 PHP 计数，因为它们加起来可能会达到一个非常大的数字。因此我希望 MySQL 返回计数。

谁能建议一个好方法来做到这一点？将其分成两个查询是可以接受的。

提前致谢。

原文

I have a situation where I need to match an objects to multiple tags simultaneously so that results set is "narrowed down" to match all tags. I've found the following MySQL query for this:

  SELECT * 
    FROM OBJECTS o
    JOIN OBJECTSTAGS ot ON ot.object_id = o.id
    JOIN TAGS t ON t.id = ot.tag_id
   WHERE t.name IN ('tag1','tag2')
GROUP BY o.id
  HAVING COUNT(DISTINCT t.name) = 2

... where 2 is the number of tags being matched. It works fine.

However, I need the query to return a count of the objects instead of the objects themselves. This query seems to confuse itself if I add COUNT(*) to the SELECT. I'm hesitant to return just the ids for example and do a PHP count of them because they could add up to a very large number. I would therefore like MySQL to return the count.

Could anyone suggest a good way to do this? Breaking it into two queries would be acceptable.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愁以何悠 2024-11-12 06:51:38

使用：

  SELECT COUNT(o.*) AS numObjects
    FROM OBJECTS o
   WHERE EXISTS (SELECT NULL
                   FROM OBJECTSTAGS ot 
                   JOIN TAGS t ON t.id = ot.tag_id
                              AND t.name IN ('tag1','tag2')
                  WHERE ot.object_id = o.id)

Use:

  SELECT COUNT(o.*) AS numObjects
    FROM OBJECTS o
   WHERE EXISTS (SELECT NULL
                   FROM OBJECTSTAGS ot 
                   JOIN TAGS t ON t.id = ot.tag_id
                              AND t.name IN ('tag1','tag2')
                  WHERE ot.object_id = o.id)

回复收藏 0 原文

~没有更多了~