mysql 逗号连接,MySQL使用逗号分隔列表-连接表

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

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

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

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

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

解决方案

This should work for you:

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

),'')

Here is the updated SQL Fiddle