mysql - 加入两个计数查询没有给出正确的结果

Question

我试图加入两个查询来比较计数

SELECT count(customer_id) , customer_id
FROM `blog_post`
group by customer_id

第二个查询是

SELECT count(customer_id)
FROM `blog_comment`
WHERE `is_admin` IS NOT NULL
group by customer_id

我创建的连接查询是

SELECT count(post.customer_id) as post_count , post.customer_id ,
       count(comment.customer_id) 
FROM `blog_post` as post 
    left join blog_comment as comment on post.customer_id = comment.customer_id 
WHERE `is_admin` IS NOT NULL 
GROUP BY post.customer_id 

我没有得到与单独运行它们相同的结果，我做错了什么

标签： mysqljoingroup-bycountunion-all

Answer 1

根据您的要求，您需要FULL OUTER JOIN2 个查询中的一个，MySql 不支持该查询，只能使用LEFT/RIGHT连接和UNION ALL.

做你想做的另一种方法是使用UNION ALL2 个查询并汇总结果：

SELECT customer_id, 
       MAX(post_count) post_count,
       MAX(comment_count) comment_count 
FROM (
  SELECT customer_id, COUNT(*) post_count, 0 comment_count
  FROM `blog_post`
  GROUP BY customer_id
  UNION ALL
  SELECT customer_id, 0, COUNT(*)
  FROM `blog_comment`
  WHERE `is_admin` IS NOT NULL
  GROUP BY customer_id
) t
GROUP BY customer_id