mysql - 连接表和计数的正确方法

Question

假设我有 3 张 table 。它们被称为用户、线程、帖子。我需要知道有多少帖子和话题用户在一个 sql 行中创建。做这个的最好方式是什么？我会做类似以下的连接，这是正确的/最好的方法吗？但是我该如何计数呢？

SELECT *,
FROM users
LEFT JOIN threads on threads.user_id=users.user_id
LEFT JOIN posts on posts.user_id=users.user_id
WHERE user_id='1'

Answer 1

您可以使用类似这样的应用和聚合函数来获取总计数:

SELECT u.user_id, 
    coalesce(TotalThreads, 0) + coalesce(TotalPosts, 0) Total
FROM users u
LEFT JOIN
(
  select user_id, count(thread_id) TotalThreads
  from threads
  group by user_id
) t
  on t.user_id=u.user_id
LEFT JOIN
(
  select user_id, count(post_id) TotalPosts
  from posts
  group by user_id
) p
  on p.user_id=u.user_id
WHERE u.user_id='1'

如果您希望值分开，则:

SELECT u.user_id, 
   coalesce(TotalThreads, 0) TotalThreads, 
   coalesce(TotalPosts, 0) TotalPosts
FROM users u
LEFT JOIN
(
  select user_id, count(thread_id) TotalThreads
  from threads
  group by user_id
) t
  on t.user_id=u.user_id
LEFT JOIN
(
  select user_id, count(post_id) TotalPosts
  from posts
  group by user_id
) p
  on p.user_id=u.user_id
WHERE u.user_id='1'