MySQL group_concat为空字段添加分隔符？
为此，您可以replace()与group_concat()一起使用。在这里，对于空字段，我们显示一个逗号作为分隔符。让我们首先创建一个表-
mysql> create table DemoTable ( Id int, Number varchar(100) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values(10,'456'); mysql> insert into DemoTable values(11,'345'); mysql> insert into DemoTable values(10,''); mysql> insert into DemoTable values(10,'278'); mysql> insert into DemoTable values(10,''); mysql> insert into DemoTable values(10,'789');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+------+--------+ | Id | Number | +------+--------+ | 10 | 456 | | 11 | 345 | | 10 | | | 10 | 278 | | 10 | | | 10 | 789 | +------+--------+ 6 rows in set (0.00 sec)
以下是为空字段添加分隔符的查询-
mysql> select Id,replace(group_concat(Number),',,',',') from DemoTable group by Id;
这将产生以下输出-
+------+----------------------------------------+ | Id | replace(group_concat(Number),',,',',') | +------+----------------------------------------+ | 10 | 456,278,789 | | 11 | 345 | +------+----------------------------------------+ 2 rows in set (0.00 sec)