【MYSQL】分组拼接列字段

create table testtb(keycol varchar(1),num int);

insert into testtb select 'A',1;
insert into testtb select 'B',1;
insert into testtb select 'A',1;
insert into testtb select 'A',2;
insert into testtb select 'B',2;
insert into testtb select 'B',3; 


mysql> SELECT keycol,group_concat(num) as nn  from 
   (
    SELECT keycol,concat(num,count(1)) num  from testtb group by keycol,num
    ) t 
   group by keycol;
+--------+----------+
| keycol | nn       |
+--------+----------+
| A      | 12,21    |
| B      | 21,31,11 |
+--------+----------+
2 rows in set, 1 warning (0.00 sec)

 

 

PS： 先统计键值 keycol 对应的值num 出现的次数。 然后将键值keycol，num，次数拼接起来。