MySQL多行合并--GROUP_CONCAT函数,转换json数组--JSON_ARRAY() 函数，转换json对象--json_object()函数

GROUP_CONCAT: 多行合并

在表设计中，一个字段可能会对应多条数据，但在有的查询场景下，需要实现将多行数据合并成一行数据，在Mysql中可以使用GROUP_CONCAT函数来实现

正常查出来是两条数据，如果需要一条，就可以用GROUP_CONCAT

GROUP_CONCAT（field1）这样默认就是一个字段用逗号拼接

select pid ,GROUP_CONCAT(name) from table，结果就是

GROUP_CONCAT（field1,'-',field2）这样默认就是两个个字段用-拼接

select pid ,GROUP_CONCAT(id,'-',name) from table，结果就是

JSON_object: 创建json对象

如果要将查出来的字段转换成json对象，可以用这个函数：JSON_OBJECT('id',id,'name',name)

select pid ,JSON_OBJECT('id',id,'name',name) from table，结果就是

json_array：创建Json数组

如果要将查出来的字段转换成json数组，可以用这个函数：json_array(id,name)

select pid ,JSON_ARRAY(id,name) from table，结果就是

这就是最简单的使用方法了，当然了，大家可以继续深化使用，

比如JSON_ARRAY(JSON_OBJECT('id',id,'name',name) )

比如 GROUP_CONCAT（JSON_OBJECT('id',id,'name',name)）

等等

放一个例子：

select `key`, json_array(GROUP_CONCAT(JSON_OBJECT('name', name, 'aid', aid,'preview_address',preview_address,'whether_receive',whether_receive,'free_or_not',free_or_not)))  as res from (SELECT 'templateList' as `key`,aid,template_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_template_material ORDER BY create_time DESC LIMIT 12) t1uNIOn allSELECT 'stickerList' as `key`,aid,sticker_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_sticker_material ORDER BY create_time DESC LIMIT 12) t2  union allSELECT 'fontList' as `key`,aid,font_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_font_material ORDER BY create_time DESC LIMIT 12) t3   union allSELECT 'brushList' as `key`,aid,brush_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_brush_material ORDER BY create_time DESC LIMIT 12) t4    union allSELECT 'dynamicList' as `key`,aid,dynamic_effect_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_dynamic_material ORDER BY create_time DESC LIMIT 12) t5   union allSELECT 'backgroundList' as `key`,aid,background_name as `name`,preview_address ,whether_receive,free_or_not from (SELECT * FROM lb_free_background_material ORDER BY create_time DESC LIMIT 12) t6   ) t group by  `key`;

来源地址：https://blog.csdn.net/zlfjavahome/article/details/129533187