Mysql 再加入一个表加入Join [英] Mysql Add another table into Join

问题描述

我有一个表，我想添加到我当前的 Join 中:

i have a table i would like to add to my current Join:

表 (report_images) 包括一个 report_id 和一个 image_path.

Table (report_images) include a report_id and a image_path.

参考:Mysql Inner Join and Group By repeating row

现在怎么样了:

["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"}]]

我的期望:

["text": "My first report", user: [{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"}], images: [{"image_path": "dog.png"}, {"image_path": "cat.png"}], totalComments: 3, 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,
       rc.COMMENTS
FROM   report 
INNER JOIN users ON users.id = report.user_id 
LEFT JOIN (
  SELECT COUNT(*) AS totalComments, report_id,
         Json_arrayagg(Json_object('text', report_comments.text, 'display_name', 
                                   users.display_name)) AS COMMENTS
  FROM report_comments 
  JOIN users ON report_comments.user_id = users.id
  GROUP BY report_id
) rc ON rc.report_id = report.id 
WHERE  report.user_id = 4 
GROUP  BY report.id, rc.COMMENTS

推荐答案

这是一个基于 CTE 的解决方案，它聚合 CTE 中的用户、图像和评论信息，然后将所有这些 CTE 加入报告将所有数据收集在一起的表格:

Here's a CTE-based solution which aggregates the user, image and comment information in CTEs and then joins all those CTEs to the report table to collect all the data together:

WITH user AS (
  SELECT id, JSON_ARRAYAGG(JSON_OBJECT('display_name', u.display_name, 'photo_url', u.photo_url)) AS user
  FROM users u
  WHERE id = 4
),
img AS (
  SELECT report_id, JSON_ARRAYAGG(JSON_OBJECT('image_path', image_path)) AS images
  FROM report_images
  GROUP BY report_id
),
cmt AS (
  SELECT report_id, 
         JSON_ARRAYAGG(JSON_OBJECT('text', rc.text, 'display_name', u.display_name)) AS comments,
         COUNT(*) AS totalcomments
  FROM report_comments rc
  JOIN users u ON rc.user_id = u.id
  GROUP BY report_id
)
SELECT r.text,
       u.user,
       img.images,
       cmt.totalcomments,
       cmt.comments
FROM report r
JOIN user u ON u.id = r.user_id
LEFT JOIN img ON img.report_id = r.id
LEFT JOIN cmt ON cmt.report_id = r.id

dbfiddle 演示

这篇关于Mysql 再加入一个表加入Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！