gpt4 book ai didi

mysql - CONCAT 列并复制到其他表 MySQL

转载 作者:行者123 更新时间:2023-11-29 00:35:08 26 4
gpt4 key购买 nike

我想做的是通过 obj_id

将一些列复制到其他表
UPDATE obj_object
INNER JOIN obj_extract as address ON (obj_object.id = address.obj_id AND address.category_id = 1)
INNER JOIN obj_extract as city ON (obj_object.id = city.obj_id AND city.category_id = 2)
INNER JOIN obj_extract as country ON (obj_object.id = country.obj_id AND country.category_id = 3)
INNER JOIN obj_extract as phone ON (obj_object.id = phone.obj_id AND phone.category_id = 4)
SET obj_object.address = address.info,
obj_object.city = city.info,
obj_object.country = country.info,
obj_object.phone = phone.info

但是一些 obj_extract 行具有相同的 category_id,我需要计算包含相同 obj_id 的记录是否有不止一行具有相同的 category_id 如果是,我需要concat 这些条目并复制到obj_object 表列。表格示例:

+----+--------------+---------+---------+
| ID | category_id | info | obj_id |
+----+--------------+---------+---------+
| 1 | 1 | test | 2 |
+----+--------------+---------+---------+
| 2 | 1 | test1 | 2 |
+----+--------------+---------+---------+
| 3 | 2 | test2 | 2 |
+----+--------------+---------+---------+

我需要:

 obj_object.address = 'test - test1', // // value from address.info
obj_object.city = 'test2', // value from city.info

最佳答案

您需要考虑使用 GROUP_CONCAT:

UPDATE obj_object o
SET o.address = (SELECT GROUP_CONCAT(info)
FROM obj_extract o2
WHERE o.id = o2.obj_id AND o2.category_id = 1),
o.city = (SELECT GROUP_CONCAT(info)
FROM obj_extract o3
WHERE o.id = o3.obj_id AND o3.category_id = 2),
o.country = (SELECT GROUP_CONCAT(info)
FROM obj_extract o4
WHERE o.id = o4.obj_id AND o4.category_id = 3),
o.phone = (SELECT GROUP_CONCAT(info)
FROM obj_extract o5
WHERE o.id = o5.obj_id AND o5.category_id = 4)

祝你好运。

关于mysql - CONCAT 列并复制到其他表 MySQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14688514/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com