mysql - 多次加入两个表

Question

我需要两个表的结果，其中一个是父表，另一个是子表以及子表本身的子表。

如果我像这样执行 sql 查询:

   SELECT cc.collection_id, cc.title, cc.type, cc.alias as forum_alias,
           SUBSTRING(cc.description,1,200) as short_desc, 
           COUNT(b1.boardmessage_id) as total_threads,
           COUNT(b2.boardmessage_id) as total_replies 

   FROM contentcollections cc 
        JOIN boardmessages b1 ON b1.parent_id = cc.collection_id  
        JOIN boardmessages b2 ON b2.collection_id = cc.collection_id 

   WHERE cc.type=1 
         AND cc.is_active=1 
         AND b1.parent_type='collection' 
         AND b1.is_active=1 
         AND b2.parent_type IN('message','reply','reply_on_reply') 
   GROUP BY cc.collection_id 
   ORDER BY cc.created DESC;

它给了我相同数量的线程总数和相同数量的回复总数的错误输出。如果我做这样的事情怎么办

SELECT cc.collection_id, cc.title,cc.type, cc.alias as forum_alias,
       SUBSTRING(cc.description,1,200) as short_desc,
       (SELECT COUNT(boardmessage_id) 
        FROM boardmessages 
        WHERE parent_type='collection' 
              AND collection_id=cc.collection_id 
              AND is_active=1) as total_threads,
       (SELECT count(boardmessage_id) 
        FROM boardmessages
        WHERE parent_type IN('message','reply','reply_on_reply') 
              AND collection_id=cc.collection_id AND is_active=1) as total_replies 
FROM contentcollections cc
WHERE cc.type=? AND cc.is_active=? 
ORDER BY cc.created DESC

它给了我正确的答案。

我怀疑我在第二个选项中使用了子查询，因此它可能会降低页面呈现的性能。

请向我提出同样的建议。我们将不胜感激任何帮助或建议。

谢谢

Answer 1

替换:

COUNT(b1.boardmessage_id) as total_threads,
COUNT(b2.boardmessage_id) as total_replies 

与:

COUNT(DISTINCT b1.boardmessage_id) as total_threads,
COUNT(DISTINCT b2.boardmessage_id) as total_replies 

如果你只想每行计算一次，而不是默认的，计算所有的组合。如果你在 b1 中有 3 行，在 b2 中有 5 行，你总共得到 15 行，并且两个计数都返回有 15 行，使用不同的标志你得到答案 3 和 5，因为它在中的 3 个不同的值b1，b2 中有 5 个不同的值。