拄杖忙学轻声码
已于 2023-11-09 02:03:54 修改
阅读量1.3w
收藏 24
点赞数 6
分类专栏： MySQL、Oracle、PostgreSQL 文章标签： mysql
版权
MySQL、Oracle、PostgreSQL
专栏收录该内容
29 篇文章0 订阅
订阅专栏
场景：
在mysql的关联查询或子查询中，函数 group_concat(arg) 可以合并多行的某列(或多列)数据为一行，默认以逗号分隔。以及分组函数和统计函数的组合使用
测试数据准备：
一、行转列函数 group_concat(arg)
1、单列合并，默认以逗号分隔
select
group_concat(ttop.user_name) as testStr
from t_table_one_parent ttop;
输出：
张三1,张三2,张三3,张三1,张三2,张三3,张三4
2、单列合并，指定冒号分隔符
select
group_concat(ttop.user_name separator ';') as testStr
from t_table_one_parent ttop;
输出：
张三1;张三2;张三3;张三1;张三2;张三3;张三4
3、单列合并，并去重
select
group_concat(distinct ttop.user_name separator ';') as testStr
from t_table_one_parent ttop;
输出：
张三1;张三2;张三3;张三4
4、多列拼接合并
select
group_concat(distinct ttop.user_name, ttop.company_code separator ';') as testStr
from t_table_one_parent ttop;
输出：
张三1123456;张三21234567;张三312345678;张三4123456789
5、多列拼接合并，列和列之间指定分隔符
select
group_concat(distinct ttop.user_name, ',', ttop.company_code separator ';') as testStr
from t_table_one_parent ttop;
输出：
张三1,123456;张三2,1234567;张三3,12345678;张三4,123456789
小结：
1、group_concat() 函数默认合并后以逗号分隔，也可以自定义分隔符
2、group_concat() 函数可以多列合并，列和列之间可以自定义分隔符
3、group_concat() 函数可以使用 distinct 进行去重合并
二、分组 group by、count()、sum() 函数的组合使用
1、分组和统计
select
user_name as userName,
count(user_name) as ctUserName
from t_table_one_parent ttop group by user_name;
输出：
2、分组和求和
select
user_name as userName,
count(user_name) as ctUserName,
sum(total_account_balance) as sumTab
from t_table_one_parent ttop group by user_name;
输出：
小结：
1、group by 分组可以配合 count() 统计函数综合使用，输出每组中的数量
2、group by 分组可以配合 sum() 求和函数综合使用，输出每组中的数字的和
3、group by 分组可以配合 count()、sum() 一起使用，输出每组中的数量以及和
三、count() 配合 case when then 的使用
脚本备份：
create table if not exists t_department_info
(
id bigint not null primary key auto_increment comment '主键id',
dept_name varchar(50) not null comment '部门名称',
dept_director varchar(20) not null comment '部门主管',
create_by bigint comment '创建人Id',
create_date datetime not null default now() comment '创建时间',
update_by bigint comment '更新人Id',
update_date datetime not null default now() on update now() comment '更新时间'
) engine = InnoDB
auto_increment = 1
default charset = utf8 comment '部门信息表';
create table if not exists t_person_info
(
id bigint not null primary key auto_increment comment '主键id',
person_name varchar(10) not null comment '人员名称',
id_number varchar(50) not null comment '省份证号',
gender varchar(5) not null comment '性别，M男、F女',
induction_date datetime null comment '入职日期',
quit_date datetime null comment '离职日期',
if_on_job tinyint(1) default 1 comment '是否在职状态，0-否，1-是',
dept_id bigint null comment '部门Id',
create_by bigint comment '创建人Id',
create_date datetime not null default now() comment '创建时间',
update_by bigint comment '更新人Id',
update_date datetime not null default now() on update now() comment '更新时间'
) engine = InnoDB
auto_increment = 1
default charset = utf8 comment '人员资料信息表';
-- 写入数据
INSERT INTO t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (1, '研发部', '张三', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
INSERT INTO t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (2, '测试部', '张三', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
INSERT INTO t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (3, '运维部', '李四', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (1, '张三', '123456789987654321', 'M', '2022-11-23 00:40:35', null, 1, 1, 1, '2022-12-22 16:40:48', null, '2022-12-22 16:40:48');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (2, '李四', '123456789987654321', 'F', '2022-11-23 00:40:35', '2022-12-23 00:54:47', 0, 1, 1, '2022-12-22 16:40:48', null, '2022-12-22 16:54:40');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (3, '王五', '123456789987654321', 'M', '2022-11-23 00:40:35', '2022-11-30 00:54:54', 0, 1, 1, '2022-12-22 16:40:48', null, '2022-12-23 02:13:29');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (4, '赵六', '123456789987654321', 'F', '2022-11-23 00:40:35', null, 1, 2, 1, '2022-12-22 16:40:48', null, '2022-12-22 16:40:48');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (5, '李七', '123456789987654321', 'M', '2022-11-23 00:40:35', null, 1, 2, 1, '2022-12-22 16:40:48', null, '2022-12-22 16:40:48');
INSERT INTO t_person_info (id, person_name, id_number, gender, induction_date, quit_date, if_on_job, dept_id, create_by, create_date, update_by, update_date) VALUES (6, '郑八', '123456789987654321', 'F', '2022-11-23 00:40:35', null, 1, 1, 1, '2022-12-22 16:41:17', null, '2022-12-22 17:00:22');
1、主从表关联查询统计示例脚本
select tdi.dept_name,
tdi.dept_director
,count(tpi.id) as allPersonNum -- 全部人数
,count(case when tpi.if_on_job = 1 then tpi.id end) as ifOnJobNum -- 在职全部人数
,count(case when tpi.if_on_job = 1 and tpi.gender = 'M' then tpi.id end) as ifOnJobMNum -- 在职男性人数
,count(case when tpi.if_on_job = 1 and tpi.gender = 'F' then tpi.id end) as ifOnJobFNum -- 在职女性人数
,count(case when tpi.if_on_job = 0 then tpi.id end) as quitNum -- 离职总人数
,count(case when tpi.if_on_job = 0 and date_format(tpi.quit_date, '%Y-%m') = date_format(now(), '%Y-%m') then tpi.id end) as quitNumThisMonth -- 本月离职人数
from t_department_info tdi
left join t_person_info tpi on tpi.dept_id = tdi.id
where 1=1
/* 主从过滤具体哪些数据参与统计 */
-- 主表条件过滤
and tdi.dept_director like '%张%'
-- 从表条件过滤
-- and tpi.person_name like '%李%'
group by tdi.dept_name, tdi.dept_director
/* 分组后过滤，哪些结果有资格查询出来 */
-- (按主表条件)此写法是通用的，比较准确，推荐
-- having count(case when tdi.dept_name like concat('%', '研发','%') then tdi.id end) > 0;
-- (按从表条件)此写法是通用的，比较准确，推荐
-- having count(case when tpi.person_name like concat('%', '王','%') then 1 end) > 0;
-- 此写法是mysql特有函数，但是此写法不太准确，不推荐
-- having group_concat(tpi.person_name) like '%王%';
-- 按条件过滤示例(having 关键字是分组函数(group by)的 where 关键字)
having 1=1 and quitNum > 0 and tdi.dept_name like '%研发%';
可见主与从表关系为一对多，而查询列中的 count() 中根据从表中的条件来判断是否统计入该条数据，符合条件的话返回给 count() 统计依据列，不符合条件返回给 count() 统计依据为 null(默认null不统计)
注意
【1】group by 分组之后是可以进行再筛选过滤的
【2】having 关键字是分组函数(group by)的 where 关键字
【3】分组后通过 having 关键字过滤，更简洁明了，脚本最外层就不需要再套一层 select * from (业务脚本) t 这样子
【4】画外音：查询去重能使用 group by 就不要使用 distinct ，因为 group by 分组之后还支持条件过滤，而 distinct 是针对整行数据去重、没有 group by 灵活
2、这样写的好处比关联多个 left join 对象这种方式的查询效率要快很多，而且还简洁明了不混乱
四、union 和 union all 联合查询基本使用方法
1、union 联合查询，示例
select
tto.user_name,
tto.pwd
from t_test_one tto
where tto.user_name = '张三'
union
select
ttt.user_name,
ttt.pwd
from t_test_two ttt
where ttt.user_name = '张三';
输出：
2、 union all 联合查询，示例
select
tto.user_name,
tto.pwd
from t_test_one tto
where tto.user_name = '张三'
union all
select
ttt.user_name,
ttt.pwd
from t_test_two ttt
where ttt.user_name = '张三';
输出：
小结：
1、union 和 union all 的作用都是多表联合查询
2、union all 查询所有记录、不过滤不去重，union 会去重查询结果
3、使用 union 和 union all 联合查询时，注意各个联合块的查询列顺序要相同，否则查询结果数据容易混乱
4、使用 union all 联合查询可以在各个联合块设置不同的查询条件，最后可以对查询结果进行统一排序、分页等操作
五、instr() 函数的使用
1、使用示例
select
id,
remark
from t_table_one
where instr(remark, 'cc');
查询结果：
小结：
1、instr() 的作用是过滤某列值包含指定的字符串
2、类似 like 模糊查询关键字
六、substring_index() 函数的使用，截取字符串
1、使用示例：
select
id,
remark,
substring_index(remark, ',', 1) as subStr
from t_table_one
where instr(remark, 'cc');
查询结果：
参数说明：
1、第一个参数为需要截取的字符串列
2、第二个参数为按指定的字符截取
3、第三个参数为指定从第几个指定的字符处开始截取
七、find_in_set() 函数的使用
1、数据准备
2、使用 instr() 查询，代码示例：
select dept_name,
dept_director,
dept_type
from t_department_info
where instr(dept_type, '1');
输出：
从输出结果可以看出，instr() 函数是包含的意思，把 2,11 这项也给查询出来了
3、使用 find_in_set() 查询，代码示例：
select dept_name,
dept_director,
dept_type
from t_department_info
where find_in_set('1', dept_type);
输出：
从输出结果可以看出，find_in_set() 函数是精准匹配的意思，2,11 这项集合中匹配不到 1 所以没有查询出来此项，符合预期
小结：
1、如果查询的列是用逗号分隔的，并且需要精准匹配，那么适合使用 find_in_set()
2、find_in_set() 和 instr() 的区别：前者是精准匹配集合中元素，后者是过滤某列值包含(contains)指定的字符串、类似 like 模糊查询关键字
————————————————
版权声明：本文为CSDN博主「拄杖忙学轻声码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hkl_Forever/article/details/116704472
1685
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
