MySQL使用逗号分隔列表-连接表 [英] Mysql working with comma separated list - Junction table

问题描述

我有一个具有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

这是表格 http://sqlfiddle.com/#!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使用逗号分隔列表-连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！