QL语句
SELECT mi.intf_file, change_typeFROM tb_mr_info miWHERE mi.intf_file IN ( 'app_mmgr.h', 'arp_mim_dynclass_pub.h' )
表数据
intf_filechange_type
app_mmgr.hadd
app_mmgr.hmodify
arp_mim_dynclass_pub.hmodify
arp_mim_dynclass_pub.hdelete
arp_mim_dynclass_pub.hmodify
arp_mim_dynclass_pub.hmodify
arp_mim_dynclass_pub.hmodify
arp_mim_dynclass_pub.hdelete
arp_mim_dynclass_pub.hmodify
arp_mim_dynclass_pub.hmodify
使用group_concat()函数,按指定字段进行分组,同一分组内按某一字段合并,默认用逗号分隔
SELECT mi.intf_file, group_concat(mi.change_type) change_typeFROM tb_mr_info miWHERE mi.intf_file IN ( 'app_mmgr.h', 'arp_mim_dynclass_pub.h' ) GROUP BY mi.intf_file
执行结果
intf_filechange_type
app_mmgr.hadd,modifyarp_mim_dynclass_pub.hmodify,delete,modify,modify,modify,delete,modify,modify
对合并结果去重
SELECT mi.intf_file,group_concat(distinct mi.change_type) change_typeFROM tb_mr_info miWHERE mi.intf_file IN ('app_mmgr.h','arp_mim_dynclass_pub.h' )GROUP BY mi.intf_file
执行结果
intf_filechange_type
app_mmgr.hadd,modify
arp_mim_dynclass_pub.hmodify,delete
指定分隔符
group_concat(distinct mi.change_type separator ' | ') change_type
执行结果
intf_filechange_type
app_mmgr.hadd | modifyarp_mim_dynclass_pub.hmodify | delete
合并并排序
SELECT mi.intf_file, group_concat(mi.id ORDER BY mi.id), group_concat( DISTINCT mi.change_type ORDER BY mi.id SEPARATOR ' | ' ) change_typeFROM tb_mr_info miWHERE mi.intf_file IN ( 'app_mmgr.h', 'arp_mim_dynclass_pub.h' ) GROUP BY mi.intf_file
执行结果
intf_filegroup_concat(mi.id order by mi.id)change_type
app_mmgr.h3982,3983add | modify
arp_mim_dynclass_pub.h4659,4660,4661,4662,4665,4666,4667,4668delete | modify