mysql - 如何通过 LEFT JOIN 添加多个表并计算每个表的行数？

Question

我正在计算我离开加入的表的结果:

SELECT p.*,COUNT(po.name) AS posts
    FROM projects p
    left join posts po on p.name = po.name
    group by p.id

http://sqlfiddle.com/#!9/3e9d4b/4

但现在我想通过 LEFT JOIN 添加另一个表并计算它:

SELECT p.*,COUNT(po.name) AS posts,
    COUNT(ta.name) AS tasks
    FROM projects p
    left join posts po on p.name = po.name
    left join tasks ta on p.name = ta.name
    group by p.id

http://sqlfiddle.com/#!9/ee068/2

但是现在数错了。对于猫，我只有 2 个帖子和 3 个任务。数字 6 是从哪里来的？

Answer 1

左连接不是执行此操作的正确工具。你应该使用子选择:

SELECT p.*,
  (SELECT COUNT(*) FROM posts po WHERE p.name = po.name) AS posts,
  (SELECT COUNT(*) FROM tasks ta WHERE p.name = ta.name) AS tasks
FROM projects p