mysql - 用多列mysql更新一列值

Question

您好，我的数据库中有一个表。

ShopID | ParentID | SELL 

1          0        2,3
2          1        1,2,3
3          1        2,3,4
4          0        5,6
5          4        5,6,7
6          4        6,7,8

我想将子商店的 SELL 值添加到父商店的 SELL 值，但不想添加重复的值，

我终于想要一张这样的 table 了

ShopID | ParentID | SELL 

1          0        1,2,3,4
2          1        1,3
3          1        2,4
4          0        5,6,7,8
5          4        5,7
6          4        6,8

MySQL 是否可行，请帮忙。提前致谢。

Answer 1

试试这个:

SELECT s1.shopid, s1.parentid, IFNULL( s2.sales, s1.SELL ) SELL
FROM shop s1
LEFT JOIN (
  SELECT parentid, GROUP_CONCAT( sell ) sales
  FROM shop
  GROUP BY parentid
)s2 ON s1.ShopId = s2.parentid;

SQL FIDDLE DEMO

更新查询:

 update shop
    SET SELL=s.SELL 
    from shop join (select s1.shopid,s1.parentid,ifnull(s2.sales,s1.SELL) SELL from shop s1 left join 
    (select parentid,group_concat(sell) sales from shop
    group by parentid) s2
    on s1.ShopId=s2.parentid) s
    on shop.shopid = s.shopid