GROUP_CONCAT连接函数使用

group_concat函数使用
GROUP_CONCAT
([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

建表语句
channel表

CREATE TABLE `c_coupon_channel` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `url` varchar(255) NOT NULL COMMENT '图片url',
  `rule` varchar(255) NOT NULL COMMENT '规则',
  `create_id` varchar(100) DEFAULT NULL COMMENT 'id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_id` varchar(100) DEFAULT NULL COMMENT '',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COMMENT='channel表';

channel_area表

CREATE TABLE `c_coupon_channel_area` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `pid` int(11) DEFAULT NULL COMMENT '关联area c_coupon_channel id',
  `province_code` varchar(255) DEFAULT NULL COMMENT 'province_code',
  `city_code` varchar(255) DEFAULT NULL COMMENT 'city_code',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COMMENT='area表';

group_concat函数使用

SELECT c.id, a.province_code, ( SELECT area_name FROM area WHERE area_code = a.province_code ) AS province, 
(SELECT group_concat((SELECT area_name FROM area WHERE area.area_code = t.city_code )) FROM c_coupon_channel_area t WHERE t.pid = c.id ) AS city,
 url, `rule`, create_id, create_time, update_id, update_time 
 FROM c_coupon_channel c 
 LEFT JOIN 
 c_coupon_channel_area a 
 ON c.id = a.pid 
 WHERE a.city_code IN (110100,130100,130200) 
 GROUP BY c.id 
 ORDER BY c.create_time DESC

使用分割符
这里的分隔符得加上 SEPARATOR ，‘具体分隔符’ ,这里以 - 作为分隔符

 select c.id,a.province_code, 
 CONCAT((SELECT area_name FROM area WHERE area_code=a.province_code),'-',GROUP_CONCAT((SELECT area_name FROM area WHERE area_code=a.city_code) SEPARATOR '-')) as channel,
 (SELECT area_name FROM area WHERE area_code=a.province_code) as province,
 group_concat(a.city_code) as cityCodes, 
 url,`rule`, create_id, create_time, update_id, update_time 
 from c_coupon_channel c 
 LEFT JOIN c_coupon_channel_area a 
  on c.id=a.pid
  WHERE a.city_code IN (110100,130100,130200) 
  GROUP BY c.id 
  ORDER BY c.create_time DESC

查询分组测试
包含查询符合条件的cityCode 数据，并将数据量大于1的数据筛选出来

 #  包含查询符合条件的cityCode 数据，并将数据量大于1的数据筛选出来
 select c.id,a.province_code, 
 CONCAT((SELECT area_name FROM area WHERE area_code=a.province_code),'-',GROUP_CONCAT((SELECT area_name FROM area WHERE area_code=a.city_code) SEPARATOR '-')) as channel,
 (SELECT area_name FROM area WHERE area_code=a.province_code) as province,
 group_concat(a.city_code) as cityCodes, 
 url,`rule`, create_id, create_time, update_id, update_time 
 from c_coupon_channel c 
 LEFT JOIN c_coupon_channel_area a 
  on c.id=a.pid
  WHERE a.city_code IN (110100,130100,130200) 
  GROUP BY c.id 
	HAVING COUNT(a.city_code) >1;
  ORDER BY c.create_time DESC
	

参考博主