数据库mysql将id相同数据合并-group_concat函数

2020-07-10  本文已影响0人  奔腾的小溪

写在前面
最近数据表新增了两个搞事情的字段,近一个月id,近一个月出现的次数。通过mysql就可以快速实现。
开始表演

create table `test1` (
     `id` int(11),
     `enbid` int(6),
     `cid` int(6),
     `abnor_id_month` varchar(50),
     `abnor_count_month` int(10)
     );
insert into test1(id,enbid,cid) values(1,12,3),(2,12,5),
(3,1,4),(4,12,5),(5,12,5),(6,1,3)(7,1,3);
update test1 k left join (select a.id as w, count(b.id) as e,group_concat(b.id) as r
 from test1 a  left join test1 b on a.enbid=b.enbid and a.cid=b.cid  and a.id !=b.id 
group by a.id )l on k.id=l.w set k.abnor_id_month=l.r;
update test1 k left join (select a.id as w, count(b.id) as e,group_concat(b.id) as r
 from test1 a  left join test1 b on a.enbid=b.enbid and a.cid=b.cid  and a.id !=b.id 
group by a.id )l on k.id=l.w set k.abnor_count_month=l.e;
上一篇下一篇

猜你喜欢

热点阅读