MySQL和和数组并列

问题描述

我有三种表格类型，文章和见解。

• 类型表格包含帖子的类型。


• 发布表格包含已发布的帖子。


• 洞察表包含日常发布的洞察力。

这里是我的sql小提琴的链接。

现在我想要生成一个报告，其中包含每个类型的帖子数以及他们的喜好和评论的总数，即Type | COUNT（post_id）| SUM（喜欢）| SUM（意见）。

这些是我的尝试：

  select type_name，count（p.post_id），sum（likes），sum（comments）
 from类型t 
 left t.type_id = p.post_type 
 left join i p.post_id = i.post_id 
 group by type_name; 
  

结果：汇总值不正确。

  select type_name，count（p.post_id），p.post_id，
（从中选择sum（喜欢）where post_id = p.post_id ）as likes，
（从post_id = p.post_id的洞察中选择总和（评论））作为评论
从类型t 
左加入帖子p on t.type_id = p.post_type 
 group by type_name; 
  

结果：仅显示一篇文章的评论和评论之和。

解决方案

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

  select type_name，count（distinct p.post_id），sum（likes）， sum（comments）
 from t 
 left t.type_id = p.post_type 
 left join join p.post_id = i.post_id 
 by type_name ; 
  

或者，您可以组合一个子查询，将同一帖子的所有洞察结合在一起：

  select type_name，count（*），sum（likes），sum（comments）
 from t 
加入帖子p on t.type_id = p.post_type 
 left join（select post_id，sum（likes）likes，sum（comments）comment 
 from insight 
 group by post_id）i on p。 post_id = i.post_id 
 group by type_name; 
  

I have three tables types, post and insights.

• Types table contains the types of post.
• post table contains the post that have been made.
• the insight table contains the insights of post on daily basis.

Here is the link to my sql fiddle SQL Fiddle.

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).

These are my tries:

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;

Result: Aggregate values are not correct.

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.

解决方案

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;

FIDDLE

这篇关于MySQL和和数组并列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！