MySQL查询多个LEFT JOIN的问题

Question

我试图通过以下查询获得所需的结果，但似乎无法正常工作...

SELECT DISTINCT
u.user_name as user_name,
u.total_points as total_points,
u.user_id as user_id,
COUNT(a.id) as user_total_articles_published,
COUNT(r.id) as user_total_replies_published,
COUNT(v.id) as user_total_votes_done
FROM users as u
LEFT JOIN articles as a ON u.user_id=a.user_id
LEFT JOIN replies as r ON u.user_id=r.user_id
LEFT JOIN votes as v ON u.user_id=v.user_id
GROUP BY u.user_id
ORDER BY u.total_points DESC
LIMIT 10

如果我删除最后两个LEFT JOIN，查询将正常工作...另外两个有什么问题吗？我需要使用其他方法才能使其正常工作吗？

谢谢。

- stergosz

---

它没有给我期望的结果...我的意思是，当我添加另外两个LEFT JOIN时，user_total_articles_published返回一个比正确答案更大的数字...所以查询肯定有问题。 - stergosz

Answer 1

我认为你所说的“不工作”是指查询返回了太多记录？这是因为连接的组合。你为每个文章记录返回每个回复，所以数字会被乘以。您可以通过在COUNT中使用DISTINCT来解决此问题。这样，您计算唯一的id，因此每篇文章只计算一次：

COUNT(distinct a.id) as user_total_articles_published,
COUNT(distinct r.id) as user_total_replies_published,
COUNT(distinct v.id) as user_total_votes_done

[编辑]

一种可能更快的解决方案，消除了DISTINCT和GROUP BY的需要：

SELECT
  u.user_name as user_name,
  u.total_points as total_points,
  u.user_id as user_id,
  (SELECT COUNT(a.id) FROM articles a 
   WHERE a.user_id = u.user_id) as user_total_articles_published,
  (SELECT COUNT(r.id) FROM replies r 
   WHERE r.user_id = u.user_id) as user_total_replies_published,
  (SELECT COUNT(v.id) FROM votes v 
   WHERE v.user_id = u.user_id) as user_total_votes_done
FROM users as u
ORDER BY u.total_points DESC
LIMIT 10

- GolezTrol

---

没错！谢谢...顺便说一下，我已经在顶部添加了DISTINCT，但我不知道我必须在每个COUNT()中重新添加它...这是我学到的东西..谢谢 - stergosz

这是个不好的主意，你的临时表可能会变得非常大。在分组之前，它将包括所有记录回复和投票表的乘积。 - varela

顶部的distinct是为了在结果中获得不同的值。计数中的distinct仅用于计算不同值的数量，而不是行的总数。它们有不同的含义，如果需要可以一起使用。 - GolezTrol

@GolezTrol MySQL可以优化子查询，但使用DISTINCT来删除结果并不是一个好主意。想象一下，如果一个用户有10K个回复和100个投票。 - varela

如果你有很多记录，那是可能的。我添加了另一种解决方案。希望这样更好。你应该检查是否有正确的索引。至少在所有表中，user_id 应该有一个索引。 - GolezTrol