mysql常用函數(shù)之group_concat()、group by、count()、case when then的使用

更新時(shí)間：2023年01月04日 14:24:19 作者：拄杖忙學(xué)輕聲碼

本文主要介紹了mysql常用函數(shù)之group_concat()、group by、count()、case when then的使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

場(chǎng)景：

在mysql的關(guān)聯(lián)查詢(xún)或子查詢(xún)中，函數(shù) group_concat(arg) 可以合并多行的某列(或多列)數(shù)據(jù)為一行，默認(rèn)以逗號(hào)分隔。以及分組函數(shù)和統(tǒng)計(jì)函數(shù)的組合使用

測(cè)試數(shù)據(jù)準(zhǔn)備：

一、行轉(zhuǎn)列函數(shù) group_concat(arg)

1、單列合并，默認(rèn)以逗號(hào)分隔

select
    group_concat(ttop.user_name) as testStr
from t_table_one_parent ttop;

輸出：
張三1,張三2,張三3,張三1,張三2,張三3,張三4

2、單列合并，指定冒號(hào)分隔符

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

小結(jié)：
1、group_concat() 函數(shù)默認(rèn)合并后以逗號(hào)分隔，也可以自定義分隔符
2、group_concat() 函數(shù)可以多列合并，列和列之間可以自定義分隔符
3、group_concat() 函數(shù)可以使用 distinct 進(jìn)行去重合并

二、分組 group by、count()、sum() 函數(shù)的組合使用

1、分組和統(tǒng)計(jì)

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;

輸出：

小結(jié)：
1、group by 分組可以配合 count() 統(tǒng)計(jì)函數(shù)綜合使用，輸出每組中的數(shù)量
2、group by 分組可以配合 sum() 求和函數(shù)綜合使用，輸出每組中的數(shù)字的和
3、group by 分組可以配合 count()、sum() 一起使用，輸出每組中的數(shù)量以及和

三、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 '部門(mén)名稱(chēng)',
    dept_director varchar(20) not null comment '部門(mén)主管',
    create_by     bigint comment '創(chuàng)建人Id',
    create_date   datetime    not null default now() comment '創(chuàng)建時(shí)間',
    update_by     bigint comment '更新人Id',
    update_date   datetime    not null default now() on update now() comment '更新時(shí)間'
) engine = InnoDB
  auto_increment = 1
  default charset = utf8 comment '部門(mén)信息表';
 
 
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 '人員名稱(chēng)',
    id_number      varchar(50) not null comment '省份證號(hào)',
    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 '是否在職狀態(tài)，0-否，1-是',
    dept_id        bigint      null comment '部門(mén)Id',
    create_by      bigint comment '創(chuàng)建人Id',
    create_date    datetime    not null default now() comment '創(chuàng)建時(shí)間',
    update_by      bigint comment '更新人Id',
    update_date    datetime    not null default now() on update now() comment '更新時(shí)間'
) engine = InnoDB
  auto_increment = 1
  default charset = utf8 comment '人員資料信息表';
 
 
 
 
 
 
-- 寫(xiě)入數(shù)據(jù)
INSERT INTO any_case.t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (1, '研發(fā)部', '張三', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
INSERT INTO any_case.t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (2, '測(cè)試部', '張三', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
INSERT INTO any_case.t_department_info (id, dept_name, dept_director, create_by, create_date, update_by, update_date) VALUES (3, '運(yùn)維部', '李四', 1, '2022-12-22 16:38:10', null, '2022-12-22 16:38:10');
 
 
INSERT INTO any_case.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 any_case.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 any_case.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 any_case.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 any_case.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 any_case.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、主從表關(guān)聯(lián)查詢(xún)統(tǒng)計(jì)示例腳本

select tdi.dept_name,
       tdi.dept_director
       ,count(tpi.id) as allPersonNum	-- 全部人數(shù)
       ,count(case when tpi.if_on_job = 1 then tpi.id end) as ifOnJobNum -- 在職全部人數(shù)
       ,count(case when tpi.if_on_job = 1 and tpi.gender = 'M' then tpi.id end) as ifOnJobMNum -- 在職男性人數(shù)
       ,count(case when tpi.if_on_job = 1 and tpi.gender = 'F' then tpi.id end) as ifOnJobFNum -- 在職女性人數(shù)
       ,count(case when tpi.if_on_job = 0 then tpi.id end) as quitNum -- 離職總?cè)藬?shù)
       ,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 -- 本月離職人數(shù)
from t_department_info tdi
left join t_person_info tpi on tpi.dept_id = tdi.id
#支持主表和從表過(guò)濾
where tdi.dept_director like '%張%'
  and (tpi.if_on_job = 0 and date_format(tpi.quit_date, '%Y-%m') = date_format(now(), '%Y-%m')) > 0
  and tpi.person_name like '%李%'
group by tdi.dept_name, tdi.dept_director;

可見(jiàn)主與從表關(guān)系為一對(duì)多，而查詢(xún)列中的 count() 中根據(jù)從表中的條件來(lái)判斷是否統(tǒng)計(jì)入該條數(shù)據(jù)，符合條件的話返回給 count() 統(tǒng)計(jì)依據(jù)列，不符合條件返回給 count() 統(tǒng)計(jì)依據(jù)為 null(默認(rèn)null不統(tǒng)計(jì))

2、這樣寫(xiě)的好處比關(guān)聯(lián)多個(gè) left join 對(duì)象這種方式的查詢(xún)效率要快很多，而且還簡(jiǎn)潔明了不混亂

到此這篇關(guān)于mysql常用函數(shù)之group_concat()、group by、count()、case when then的使用的文章就介紹到這了,更多相關(guān)mysql group_concat()、group by、count()、case when then內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: