GROUP_CONCAT的使用

一、环境介绍

1.部门表

CREATE TABLE `t_corp_dept` (
  `dept_id` varchar(32) NOT NULL DEFAULT '0' COMMENT '机构ID',
  `dept_name` varchar(255) NOT NULL COMMENT '机构名称',
  `dept_short_name` varchar(60) NOT NULL COMMENT '企业简称',
  `parent_id` varchar(32) DEFAULT NULL COMMENT '父机构ID，根机构该字段为null',
  `level` int(11) NOT NULL COMMENT '层级：1,2,3,4,5；默认为1；',
  `top_dept_id` varchar(32) NOT NULL COMMENT '顶级机构ID',
  PRIMARY KEY (`dept_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.车辆表

CREATE TABLE `t_corp_vehicle` (
  `vehicle_id` varchar(32) NOT NULL DEFAULT '' COMMENT '车辆ID',
  `vehicle_nick_name` varchar(50) DEFAULT NULL COMMENT '车辆名称：长度最大15位，支持字母、数字、下划线',
  `device_id` varchar(32) DEFAULT NULL COMMENT '设备序列号：只支持数字',
  `device_IMSI` varchar(32) DEFAULT NULL COMMENT '设备IMSI号',
  `dept_id` varchar(32) NOT NULL COMMENT '企业或机构ID',
  PRIMARY KEY (`vehicle_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、查询某个企业下所有的车辆信息

1.查询出企业下所有部门id的集合

SELECT GROUP_CONCAT(dept_id) FROM  t_corp_dept where top_dept_id='145de4c406e14a9994eda54697633d79'

输出结果为

00e2f807c6e440819431309ca6db50a3,013ec35d7b41423aa295db13f7c0c2f1,014cd17cbe554812ab1abe0fa5659534

2.将结果用引号和逗号分割开来

public class Demo1 {
    public static void main(String[] args) {
        String str = "00e2f807c6e440819431309ca6db50a3,013ec35d7b41423aa295db13f7c0c2f1,014cd17cbe554812ab1abe0fa5659534";
        String str1 = str.replaceAll (",", "\",\"");
        System.out.println(str1);
    }
}

输出结果：

00e2f807c6e440819431309ca6db50a3","013ec35d7b41423aa295db13f7c0c2f1","014cd17cbe554812ab1abe0fa5659534

也可以用 ALT+H将逗号替换成’逗号’ 。这样做更加的快捷。

3.在结果最前面、最后面分别加一个"

"00e2f807c6e440819431309ca6db50a3","013ec35d7b41423aa295db13f7c0c2f1","014cd17cbe554812ab1abe0fa5659534"

4.执行SQL

SELECT GROUP_CONCAT(dept_id) FROM  t_corp_dept where top_dept_id='145de4c406e14a9994eda54697633d79'

SELECT
	t1.id,
	t1.alarm_id,
	t1.vehicle_id,
	t1.device_no,
	t1.mobile,
	t1.msg_type,
	t1.content,
	date_format( t1.alarm_time, '%Y-%m-%d %H:%i:%s' ) alarm_time,
	date_format( t1.finish_time, '%Y-%m-%d %H:%i:%s' ) finish_time,
	t1.address,
	t1.longitude,
	t1.latitude,
	t1.STATUS,
	t1.points,
	date_format( t1.create_time, '%Y-%m-%d %H:%i:%s' ) create_time,
	date_format( t1.deal_time, '%Y-%m-%d' ) deal_time,
	t1.user_name,
	t1.remark,
	t1.fee,
	t1.deal_user_id,
	t1.cur_mt_mileage,
	t1.cur_examine_date,
	t1.cur_insurance_date,
	t1.expiration_time,
	v.licenseplate,
	v.driver_id,
	v.examine_date,
	v.insurance_date,
	v.last_mt_date,
	v.last_examine_date,
	v.last_insurance_date,
	v.cur_mileage,
	v.next_mt_mileage,
	v.last_mt_mileage,
	v.inter_mt_date,
	dp.dept_name,
	a.NAME alarmName 
FROM
	t_corp_alarm_his t1
	LEFT JOIN t_corp_alarm_type a ON a.id = t1.alarm_id
	LEFT JOIN t_corp_vehicle v ON v.vehicle_id = t1.vehicle_id
	LEFT JOIN t_corp_dept dp ON dp.dept_id = v.dept_id 
WHERE
	dp.top_dept_id = '00443d203ddf45728651db7bc44beb64' 
	AND dp.dept_id IN ("00e2f807c6e440819431309ca6db50a3","013ec35d7b41423aa295db13f7c0c2f1","014cd17cbe554812ab1abe0fa5659534") 
	AND t1.alarm_id = 6 
	AND a.type_id = 1 
	AND t1.alarm_time >= "2022-06-01 00:00:00" 
	AND t1.alarm_time <= "2022-06-30 23:59:59" 
ORDER BY
	t1.create_time DESC

三、为什么要这么用

1.在开发的过程中，经常要处理一些生产环境的售后问题；
2.某些SQL都是各种连表查询；
3.某些企业下面的部门非常多，无法一个个的人工组装；
所以在外面直接将需要查询的条件组装好，然后放入下面这种情景中，可以增加处理效率。

dp.dept_id IN ("00e2f807c6e440819431309ca6db50a3","013ec35d7b41423aa295db13f7c0c2f1","014cd17cbe554812ab1abe0fa5659534")