MySQL 总和，用 group by 和 join 计数

2021-12-17 00:00:00 join group-by select mysql aggregate-functions

我有三种表格类型，帖子和见解.

I have three tables types, post and insights.

  • 类型表包含帖子的类型.
  • post 表包含已发布的帖子.
  • 洞察力表包含每日帖子的洞察力.

这是我的 sql fiddle SQL Fiddle 的链接.

Here is the link to my sql fiddle SQL Fiddle.

现在我想生成一个报告，其中包含针对每种类型的帖子数量以及他们喜欢和评论的总和，即类型 |COUNT(post_id) |SUM(喜欢) |SUM(评论).

Now i want to generate a report which contains number of post against each type and the sum of their likes and comments i.e. Type | COUNT(post_id) | SUM(likes) | SUM(comments).

这些是我的尝试:

select type_name, count(p.post_id), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join insights i on p.post_id = i.post_id
group by type_name;

结果:聚合值不正确.

select type_name, count(p.post_id), p.post_id, 
  (select sum(likes) from insights where post_id = p.post_id) as likes, 
  (select sum(comments)from insights where post_id = p.post_id) as comments
from types t
left join posts p on t.type_id = p.post_type
group by type_name;

结果:仅显示一个帖子的点赞和评论总数.

Result: Displays the sum of likes and comments of only one post.

推荐答案

您的第一次尝试非常接近.但是每个 post_id 都乘以 insights 中的匹配数，因此您需要使用 DISTINCT:

Your first attempt was real close. But each post_id was being multiplied by the number of matches in insights, so you need to use DISTINCT:

select type_name, count(distinct p.post_id), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join insights i on p.post_id = i.post_id
group by type_name;

或者，您可以使用组合了同一帖子的所有见解的子查询进行分组:

Alternatively, you can group with a subquery that combines all the insights for the same post:

select type_name, count(*), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join (select post_id, sum(likes) likes, sum(comments) comments
           from insights
           group by post_id) i on p.post_id = i.post_id
group by type_name;

小提琴

相关文章