GROUP_CONCAT: 多行合并
在表设计中，一个字段可能会对应多条数据，但在有的查询场景下，需要实现将多行数据合并成一行数据，在MySQL中可以使用GROUP_CONCAT函数来实现
id | pid | name |
2 | 1 | 篮球 |
3 | 1 | 足球 |
正常查出来是两条数据，如果需要一条，就可以用GROUP_CONCAT
GROUP_CONCAT（field1）这样默认就是一个字段用逗号拼接
select pid ,GROUP_CONCAT(name) from table，结果就是
pid | name |
1 | 篮球，足球 |
GROUP_CONCAT（field1,'-',field2）这样默认就是两个个字段用-拼接
select pid ,GROUP_CONCAT(id,'-',name) from table，结果就是
pid | name |
1 | 2-篮球，3-足球 |
json_object: 创建Json对象
如果要将查出来的字段转换成json对象，可以用这个函数：JSON_OBJECT('id',id,'name',name)
select pid ,JSON_OBJECT('id',id,'name',name) from table，结果就是
pid | name |
1 | {"id": 2, "name": "篮球"} |
1 | {"aid": 3, "name": "足球"} |
json_array：创建Json数组
如果要将查出来的字段转换成json数组，可以用这个函数：json_array(id,name)
select pid ,JSON_ARRAY(id,name) from table，结果就是
pid | name |
1 | [2, "篮球"] |
1 | [3, "足球"] |
这就是最简单的使用方法了，当然了，大家可以继续深化使用，
比如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
