获取多个 MySQL 表的关联记录总数

Question

我有以下查询，联合两个表，其中包含与两个单独实体关联的标签：猫和狗。我正在尝试创建一个表格，列出标签及其在两个表格中出现的次数。这是一个规范化的标记系统，因此 cat_tags 和dog_tags 表中仅引用标记 ID，这就是为什么我要执行另一个 INNER JOIN 来获取实际标记值。

SELECT x.tag_id, (COUNT(y.tag_id) + COUNT(z.tag_id)) AS num, tag_name AS tag
FROM (SELECT dt.tag_id FROM dog_tags dt UNION SELECT st.tag_id FROM cat_tags st) x
LEFT JOIN dog_tags y ON y.tag_id = x.tag_id
LEFT JOIN cat_tags z ON z.tag_id = x.tag_id
INNER JOIN tags t ON x.tag_id = t.tag_id
GROUP BY x.tag_id ORDER BY num DESC LIMIT 0,100

问题在于，对于在两个表中多次出现的标签，计数不正确。例如，标签 ID 号 5（“领子”）在 cat_tags 中出现两次，在dog_tags 中出现两次，但是上述查询给出的总计数为 8，而不是 4。另一个出现 3 次的标签则为 6。将它们乘以2. 它是什么？

Answer 1

试试这个：

select t.tag_id, t.tag_name as tag,
    ifnull(dc.dog_total, 0) + ifnull(cc.cat_total, 0) as num
from
    tags t
    left join (
        select tag_id, count(*) as dog_total
        from dog_tags
        group by tag_id
    ) as dc on t.tag_id = dc.tag_id
    left join (
        select tag_id, count(*) as cat_total
        from cat_tags
        group by tag_id
    ) as cc on t.tag_id = cc.tag_id
order by num desc
limit 0, 100

嗯，问题出在查询中的联接。因为您没有对每个源表按 tag_id 进行分组。因此，如果 collar 在 dog_tags 中出现 2 次，在 cat_tags 中出现 3 次，则连接将导致 6 行共享相同的 tag_id< /code>，所以计数是错误的。请记住，联接是每个联接表的行与特定条件的笛卡尔积。因此，通过首先按每个源表的 tag_id 进行分组，我们可以确保 tag_id 在每个表或派生表中仅出现一次。当我们连接表时，每个 tag_id 将生成一行。

Answer 2

我认为您的查询非常复杂，您可以尝试这样的操作：

  SELECT tag_id
       , tag_name
       , sum(num) as num
    FROM tags
    join
      (
          SELECT tag_id, count(*) as num FROM dog_tags GROUP BY tag_id
          union all
          SELECT tag_id, count(*) as num FROM cat_tags GROUP BY tag_id
      ) as AnimalsCount on AnimalsCount.tag_id = tags.tag_id
GROUP BY tag_id
       , tag_name -- you can remove this if you are 100% sure is not necessary

顺便说一句，检查 union 和 union all 之间的区别： mysql.com/doc/refman/5.0/en/union.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/union.html。