我有一个具有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小提琴
更多推荐
MySQL使用逗号分隔列表
发布评论