MySQL使用逗号分隔列表

本文介绍了MySQL使用逗号分隔列表-连接表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个具有ProductID和Accessory列的Junction表:

I have a Junction table with ProductID and Accessory column:

TABLE1

ProductID Accessory 1 2 1 3 2 1 2 4 2 5 3 4 1 5 2

这意味着对于ProductID 2，它具有附件ProductID 1,4和5 ...

It means that for the ProductID 2, it has the Accessory ProductIDs 1,4 and 5 ...

，我在下面的表2中看起来像这样 已经提供了GRP和ProductID，我们需要获取配件.

and i have THE TABLE 2 below which look like this THE GRP and ProductID is already provided, we need to fetch the accesories.

TABLE2

GRP ProductID accessories a 2 b 3 c 1 d 4 e 5

所以实际上，如果使用UPDATE就会像这样

so actually if using UPDATE it would be like this

TABLE2

UPDATE table2 t2 set t2.accessories = (SELECT GROUP_CONCAT(DISTINCT t1.Accessory) FROM table1 t1 WHERE t1.ProductID = t2.ProductID) GRP ProductID accessories a 2 1,4,5 b 3 c 1 2,3 d 4 1 e 5 2

但是我想根据t2.ProductID更改具有GRP字符的t2.accessories中的productID，以使FINAL表如下所示.

but i want to change the productIDs in the t2.accessories with the GRP character instead according to the t2.ProductID so that the FINAL table looks like this .

TABLE2

GRP ProductID accessories a 2 c,d,e b 3 c 1 a,b d 4 c e 5 a

这是表格 sqlfiddle/#!9/83ec9

推荐答案

这应该对您有用:

UPDATE table2 T SET T.accessories = IFNULL((SELECT foo FROM (SELECT t1.ProductID,GROUP_CONCAT(t2.GRP) as foo FROM table1 t1 LEFT JOIN table2 t2 ON t1.Accessory = t2.ProductID GROUP BY t1.ProductID ) AS bar WHERE ProductID = T.ProductID ),'')

这是更新的 SQL小提琴