Mysql 内连接和分组通过重复行 [英] Mysql Inner Join and Group By repeating row

问题描述

我对评论表有更多评论有疑问.然后User 表在报告组中重复它的值越多.它应该只获取用户一次，而评论则是当前行为.但我希望用户被提取一次.

I have problem with that more comments the comment table has. Then the more the User table repeating it value in the report group. It should only fetch user once, while comments as many there is which it is the current behaviour. But I would like the user being fetched once.

现在怎么样了:

["text": "My first report",
user: [{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png",
{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"},
{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"}}],
comments: [{"text": "Great Report", display_name: "Xavier"},
{"text": "Bad Report", display_name: "Logan"},
{"text": "Thanks for the feedback", display_name: "Cyclops"}]]

我的期望:

["text": "My first report",
user: [{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"}],
comments: [{"text": "Great Report", display_name: "Xavier"},
{"text": "Bad Report", display_name: "Logan"},
{"text": "Thanks for the feedback", display_name: "Cyclops"}]]

代码:

SELECT report.text, 
       Json_arrayagg(Json_object('display_name', users.display_name, 'photo_url', users.photo_url)) AS USER, 
       Json_arrayagg(Json_object('text', report_comments.text, 'display_name', report_comments.user_id)) AS COMMENTS 
FROM   report 
       INNER JOIN users 
               ON users.id = report.user_id 
       LEFT JOIN report_comments 
              ON report_comments.report_id = report.id 
WHERE  report.user_id = :userId 
GROUP  BY report.id 

推荐答案

假设 report to users 是 1:1 关系，那么您应该能够通过在派生表中进行评论聚合并加入该表来解决您的问题:

Assuming report to users is a 1:1 relationship, then you should be able to resolve your issue by doing the comment aggregation in a derived table, and joining to that instead:

SELECT report.text, 
       Json_arrayagg(Json_object('display_name', users.display_name, 'photo_url' 
                     , 
                     users.photo_url))                       AS USER,
       rc.COMMENTS
FROM   report 
INNER JOIN users ON users.id = report.user_id 
LEFT JOIN (
  SELECT report_id,
         Json_arrayagg(Json_object('text', report_comments.text, 'display_name', 
                                   report_comments.user_id)) AS COMMENTS
  FROM report_comments 
  GROUP BY report_id
) rc ON rc.report_id = report.id 
WHERE  report.user_id = :userId 
GROUP  BY report.id 

如果一个报告可以有多个用户，您将需要以相同的方式在派生表中聚合用户信息.

If a report can have multiple users, you will need to aggregate the user information in a derived table in the same manner.

这篇关于Mysql 内连接和分组通过重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！