mysql - 如何更新需要填充多个信息的表的单列

Question

我有如下三个表

1. manufacturing_detail_history
2. 制造产品历史
3. 制造产品

manufacturing_detail_history 有两列primarykey、description

manufacturing_products_history有两列primarykey和manu_product_id(主键是making_detail_history主键引用)

manufacturing_products 有三列 manu_product_id、名称、类型

需要在 Manufacturing_detail_history 中添加新列 manu_product_names 并使用逗号分隔值填充制造产品名称。

不确定如何为每个 manu_product_id 获取关联的多个 manu_product_name 并将其设为逗号分隔值并插入 manu_product_names 列

提前致谢。

Answer 1

Select 
    T1.ID, T1.Desc, 
    T2.manu_product_id, 
    GROUP_CONCAT(T3.name ORDER BY T3.name ASC SEPARATOR ',')
From
    manufacturing_detail_history AS T1
inner join 
    manufacturing_products_history AS T2 ON T1.ID = T2.ID
inner join
    manufacturing_products AS T3 ON T3.manu_product_id = T2.manu_product_id
GROUP BY T1.ID, T1.Desc, T2.manu_product_id;