我知道这不是迄今为止最干净的代码，但我不明白为什么我不能让它工作。
我想用字段c.ClientNumber中的Customer Numbers填充字段m.customersTemp。但只有当找到来自c.EmailAddress的LIKE匹配时，m.Emails…m.Emails才是一个包含电子邮件列表的字段。下面的代码。
UPDATE market m, customer c
SET m.customersTemp = CONCAT(m.customersTemp, c.ClientNumber)
WHERE m.Emails LIKE CONCAT('%', TRIM(c.EMailAddress), '%')
AND TRIM(c.EMailAddress)<>''
字段m.customersTemp中的结果只显示一个值(客户编号(。。。我知道有很多比赛。
TABLE CUSTOMER
ClientNumber | EMailAddress
1234 a@a.com
4567 b@b.com
2222
1111 d@d.com
-------------------------------------------------------------
| TABLE MARKET |
-------------------------------------------------------------
| ID | Emails | customersTemp|
-------------------------------------------------------------
|1 | a@a.com, b@b.com, c@c.com | |
|2 | a@a.com, b@b.com, g@g.com | |
|3 | e@e.com | |
|4 | f@f.com | |
-------------------------------------------------------------
ID为1和2的customerTemp中的结果只有1个ClientNumber。4567
不要忘记阅读底部的警告，说明为什么永远不应该这样保存数据。
您可以在备份副本上对此进行测试。我不会把它与你们的主桌相抗衡。有人说："来，试试这个删除命令，我认为它会起作用。">
-- drop table customer;
create table customer
( ClientNumber int,
EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'john@john.com'),
(4567,'joe@joe.com'),
(2222,''),
(1111,'somone@someone.com'),
(5454,'john@john.com');
-- drop table market;
create table market
( Emails varchar(100),
customersTemp varchar(100)
);
insert market(Emails,customersTemp) values
('john@john.com',''),
('joe@joe.com',''),
('test@test.com',''),
('more@more.com','');
更新声明：
UPDATE market
INNER JOIN
( SELECT c.EMailAddress as e,GROUP_CONCAT(c.ClientNumber ORDER BY c.ClientNumber) theList
FROM customer c
GROUP BY c.EMailAddress
) xDerived1
ON market.EMails = xDerived1.e
SET market.customersTemp = xDerived1.theList;
结果：
select * from market;
+---------------+---------------+
| Emails | customersTemp |
+---------------+---------------+
| john@john.com | 1234,5454 |
| joe@joe.com | 4567 |
| test@test.com | |
| more@more.com | |
+---------------+---------------+
版本2
drop table customer;
create table customer
( ClientNumber int,
EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'a@a.com'),
(4567,'b@b.com'),
(2222,''),
(1111,'d@d.com'),
(8484,'g@g.com');
-- select * from customer;
drop table market;
create table market
( id int auto_increment primary key,
Emails varchar(100),
customersTemp varchar(3000)
);
insert market(Emails,customersTemp) values
('a@a.com,b@b.com,c@c.com',''),
('a@a.com,b@b.com,g@g.com',''),
('e@e.com',''),
('f@f.com','');
-- select * from market;
drop table if exists marketHelper7;
create table marketHelper7
( -- btw this might be the kind of table
-- as an intersect/junction table that you
-- should have to begin with
-- and not have your CSV stuff
cid int not null,
mid int not null
);
insert marketHelper7 (cid,mid)
select c.ClientNumber,m.id as MarketId
from customer c
join market m
on find_in_set(c.EMailAddress,m.Emails)>0;
update market set customersTemp=''; -- do a reset
UPDATE market m
join
( SELECT mh.mid as i,GROUP_CONCAT(mh.cid ORDER BY mh.cid) theList
FROM marketHelper7 mh
GROUP BY mh.mid
) xDerived1
ON m.id = xDerived1.i
SET m.customersTemp = xDerived1.theList;
drop table marketHelper7;
select * from market;
+----+-------------------------+----------------+
| id | Emails | customersTemp |
+----+-------------------------+----------------+
| 1 | a@a.com,b@b.com,c@c.com | 1234,4567 |
| 2 | a@a.com,b@b.com,g@g.com | 1234,4567,8484 |
| 3 | e@e.com | |
| 4 | f@f.com | |
+----+-------------------------+----------------+
上面的版本2有helper表。
警告：顺便说一句，千万不要这样保存你的数据。这太疯狂了，表演也太糟糕了。请参阅我在连接表(多对多(上的回答(类似于关联表或"项目有"表，也称为一对多(。它们都是在查询过程中使用数据规范化最佳实践和快速索引的相同概念。此外，您也不会一直与数据作斗争，也不会怀疑是否使用group_concat破坏了缓冲区大小。
请注意，group_concat()在分隔符选择和函数调用内部烘焙的order by方面具有灵活性。
group_concat输出的最大长度取决于系统变量group_concat_max_len，该变量可能默认为1K，但可以设置为至少4GB。
Percona关于group_concat()的文章，以及group_concat()和find_in_set()的手册页面。