MySQL UPDATE 与 GROUP_CONCAT 连接三个表

Question

目标:使用来自 T3 的 GROUP_CONCAT 信息更新 T1，并跨 T2 进行 JOIN。

这是表结构的简化版本:

T1:xfer_lectures
相关字段:lecture_id、topics
(我正在尝试使用为该讲座指定的一系列主题的串联列表来填充主题字段。)

T2:calendar_lecture_topics
相关字段:event_id、topic_id
(T1.lecture_id = T2.event_id)

T3:lecture_topics
相关字段:id、title
(T2.topic_id = T3.event_id)

我可以通过以下查询成功地选择我想要的信息:

SELECT 
    T1.`lecture_id`, GROUP_CONCAT(DISTINCT `title` SEPARATOR '; '), COUNT(*)
FROM
    `xfer_lectures` T1
    INNER JOIN
        `calendar_lecture_topics` T2
    INNER JOIN
        `lecture_topics` T3 
    ON T1.`lecture_id` = T2.`event_id`
    AND T2.`topic_id` = T3.`id`
    GROUP BY T1.`lecture_id`

但是，当我尝试使用串联信息更新 T1 时，我失败了。我已经尝试了很多版本的更新查询，其中大部分都会产生错误。该查询作为有效查询运行，但使用主题表中所有主题的相同列表填充每个主题字段:

 UPDATE 
    `xfer_lectures` T1
 JOIN `calendar_lecture_topics` T2
    ON T1.`lecture_id`=T2.`event_id`
 JOIN `lecture_topics` T3
    ON T2.`topic_id` = T3.`id`
 SET T1.`topics` = (
    SELECT 
    GROUP_CONCAT(`title` SEPARATOR '; ')
    FROM `lecture_topics`
    )

我还尝试了 SELECT 语句包含 GROUP_BY 子句的版本，但我最终还是得到了每条记录的相同主题列表，而不是每节课只有两三个相关主题。例如:

SET T1.`topics` = (       
SELECT        
GROUP_CONCAT(`title` SEPARATOR '; ')       
FROM `lecture_topics`
WHERE T2.`topic_id` = T3.`id` 
AND T1.`lecture_id`=T2.`event_id`
GROUP BY T2.`event_id`)

我哪里错了？我对复杂的查询不是很有经验，所以我对 JOIN 和分组的理解可能有缺陷。非常感谢任何帮助!

Answer 1

• SELECT GROUP_CONCAT(title SEPARATOR '; ') FROM lecture_topics) 基本上会以字符串形式返回 ALL lecture_topics 表中的标题。这就是为什么您的 SET 查询使用相同的字符串(包含所有标题)更新所有讲座
• 您基本上需要使用 Derived Table这里。在此派生表中，您将根据 event_id (lecture_id) 的分组获取标题。
• 现在，在 event_id = lecture_id 上使用 xfer_lectures 加入此表，并使用 Group_concat()派生表的结果更新 xfer_lectures 表中的值。

试试这个:

 UPDATE 
    `xfer_lectures` AS T1
 JOIN ( SELECT 
          T2.`event_id`, 
          GROUP_CONCAT(T3.`title` SEPARATOR '; ') as `topics`
        FROM `calendar_lecture_topics` AS T2 
        JOIN `lecture_topics` AS T3 
          ON T2.`topic_id` = T3.`id` 
        GROUP BY T2.`event_id`
       ) AS T4 ON T4.`event_id` = T1.`lecture_id` 
 SET T1.`topics` = T4.`topics`