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

最新推荐文章于 2024-09-20 00:00:00 发布

zlfjavahome

最新推荐文章于 2024-09-20 00:00:00 发布

阅读量4.7k

点赞数

文章标签： sql mysql java Powered by 金山文档

本文链接：https://blog.csdn.net/zlfjavahome/article/details/129533187

版权

文章介绍了在MySQL中如何使用GROUP_CONCAT函数合并多行数据为一行，以及如何利用JSON_OBJECT和JSON_ARRAY函数将查询结果转换为JSON对象和数组。示例展示了如何结合这些函数进行复杂的数据转换操作，如创建包含多个字段的JSON结构。

摘要由CSDN通过智能技术生成

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) t1
union all
SELECT '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 all
SELECT '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 all
SELECT '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 all
SELECT '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 all
SELECT '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`;