mysql - 使用 COUNT 过滤标签

Question

我有以下查询，它根据一系列约束过滤掉线程行:

SELECT *
    FROM threads th 
        JOIN v_threads_with_tags AS twt      /*Only if filtering by tags*/
            ON th.thread_id = twt.thread_id
        JOIN v_friends AS f                  /*Only if filtering to threads from friends*/
            ON th.owner_id = f.friend 
        LEFT JOIN v_visible_threads AS v        
            ON v.thread_id = th.thread_id
    WHERE (v.viewer_id = 43 || v.viewer_id IS NULL) 
    && (v.friend_id = 43 || v.friend_id IS NULL)      
    && user = 43;                          
    && tag_name IN ('foo','bar')

部分查询我还没有机会测试，但我可以肯定地说标签名称没有被完全过滤。此查询将返回与此类似的结果集(仅列出相关列):

thread_id | tag_name
    1         foo
    1         bar
    2         foo
    3         foo

我想要一个仅包含 thread_id 的结果集，该结果集链接到查询中列出的所有标记，显然我不能有重复项。在上面的示例中，我想要一个仅包含线程 #1 的一个实例的结果集。

我在 stackoverflow 上看到过类似的问题(请在将其标记为重复之前阅读)，虽然提供的解决方案差异很大，但一般路线似乎经常涉及在查询末尾添加以下语句:

HAVING COUNT('tag_name') = 2

我也尝试过以下类似的查询:

SELECT th.thread_id,th.owner_id,th.message,th.time,tag_name,viewer_id,v.friend_id
    FROM threads th 
        LEFT JOIN v_visible_threads AS v
            ON v.thread_id = th.thread_id
    WHERE (v.viewer_id = 43 || v.viewer_id IS NULL) 
    && (v.friend_id = 43 || v.friend_id IS NULL)
    && th.thread_id IN 
    (
        SELECT thread_id FROM v_threads_with_tags
        WHERE tag_name IN ('foo','bar')
        HAVING COUNT(tag_name) = 2
    )

我无法理解 COUNT() 的用法在其中任何一个中。在此结果集中，列 tag_name 有四个值，因此我希望 COUNT(tag_name) 返回 4，无论特定行中 tag_name 的值是什么。这实际上是它返回的值，因此该语句导致查询返回一个空集。

不过，我发现这个语句在任何地方都被用来解决此类问题，所以我必须假设每个人都正确使用它，并且我遗漏了一些东西。

有人可以向我解释一下我是否正确理解了 COUNT，以及我可以使用什么方法来完成查询？

Answer 1

如果您想要具有所有标记的线程，则不能仅使用where子句来获得它。相反，按 thread_id 聚合并计算每个标签的匹配数。返回与您关心的每个标签至少有一个匹配的线程:

SELECT th.thread_id
FROM threads th JOIN
     v_threads_with_tags twt      /*Only if filtering by tags*/
     ON th.thread_id = twt.thread_id JOIN
     v_friends f                  /*Only if filtering to threads from friends*/
     ON th.owner_id = f.friend LEFT JOIN
     v_visible_threads AS v        
     ON v.thread_id = th.thread_id
WHERE (v.viewer_id = 43 || v.viewer_id IS NULL) and
      (v.friend_id = 43 || v.friend_id IS NULL) and   
     user = 43
group by th.thread_id
having sum(tag_name = 'foo') > 0 and
       sum(tag_name = 'bar') > 0;