mysql查询组错误结果

Question

我正在为网站创建一个简单的论坛应用程序，这是我的表架构...

这是获取所有类别、total_topics、total_posts 和 last_topic_date 的 mysql 查询...

SELECT fc.id, fc.name, fc.var_name, fc.description, fc.dnt, COUNT(ft.id) AS total_topics, COUNT(fp.id) AS total_posts, MAX(ft.dnt) AS last_topic_date 
FROM forum_categories fc
LEFT JOIN forum_topics ft ON ft.category_id = fc.id
LEFT JOIN forum_posts fp ON fp.topic_id = ft.id
GROUP BY fc.id

mysql fiddle

如您所见，我得到了 3 个 total_topic，而该类别中只有 2 个主题可用。请帮我解决这个问题。谢谢。

Answer 1

在此处查看已解决的答案:http://sqlfiddle.com/#!2/ad2a6/3

SELECT 
    fc.id, 
    fc.name, 
    fc.var_name, 
    fc.description, 
    fc.dnt, 
    COUNT(DISTINCT ft.id) AS total_topics, 
    COUNT(DISTINCT fp.id) AS total_posts, 
    MAX(ft.dnt) AS last_topic_date -- oldest date for most recent use MIN(ft.dnt) AS last_topic_date 
FROM forum_categories fc
LEFT JOIN forum_topics ft ON ft.category_id = fc.id
LEFT JOIN forum_posts fp ON fp.topic_id = ft.id
GROUP BY fc.id

当您离开加入时，您并没有过滤掉空记录，因此您必须在计数中澄清 DISTINCT 以获得正确的 COUNT()。 INNER JOINS 通常更快并且可能是更好的解决方案，除非您不想过滤。