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屋!