Mysql:选择并分组

编辑:因为“TABLE”对于缺少的tablename是一个糟糕的选择,所以我假设您的表被调用 Posts .

将表与自身连接:

SELECT * FROM Posts P1
JOIN Posts P2
ON P1.POST_ID = P2.POST_ID
WHERE P1.TAG_NAME = 'tag1'
AND P2.TAG_NAME = 'tag2'

我只是把这个(未测试的)依赖子查询解决方案留在这里作为参考,尽管一旦您进入大型数据集,它可能会非常慢。任何使用连接执行相同操作的解决方案都应该选择此解决方案。

假设你有一个 posts 带有 id 以及字段:

SELECT count(*) FROM posts WHERE EXISTS(SELECT NULL FROM posts_tags WHERE tag = 'tag1' AND post_id = posts.id) AND EXISTS(SELECT NULL FROM posts_tags WHERE tag = 'tag2' AND post_id = posts.id)

请尝试以下查询:

SELECT COUNT(*) nb_posts
FROM (
    SELECT post_id, COUNT(*) nb_tags
    FROM table
    WHERE tag_name in ('tag1','tag2')
    GROUP BY post_id
    HAVING COUNT(*) = 2
  ) t

编辑 :根据Konerak答案,以下是处理给定帖子的标记名重复时的情况的查询:

SELECT DISTINCT t1.post_id
FROM table t1
  JOIN table t2
    ON t1.post_id = t2.post_id
       AND t2.tag_name = 'tag2'
WHERE t1.tag_name = 'tag1'