写在前面
最近开发过程中,遇到一个需求是要将所查询的多条结果汇总成一条结果展示,由于之前没有接触过这方面的业务,所以经过一番折腾之后,解决了需求,这里特此记录一下,以供后续参考!
1、问题复现
这里以一个例子进行说明:
需求:一个员工每月是否完成了打卡,要求统计员工当月完成和未完成日期,展示结果如下:
测试的数据库表字段如下:
复制代码
CREATE TABLEtime_summary
(
id
int NOT NULL AUTO_INCREMENT,
emp_id
varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘员工号’,
emp_name
varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘员工姓名’,
time_date
date DEFAULT NULL COMMENT ‘填报日期’,
finish_flag
varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘完成标志:0:未完成,1:已完成’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
复制代码
测试数据如下:
View Code
这种情况下,我们一般可以将所有的情况查询出来(这里以6月份数据为例),查询SQL如下:
SELECT t.emp_id,t.emp_name,t.time_date,t.finish_flag from time_summary t
where t.time_date >= ‘-06-01’ and time_date <= ‘-06-30’
这样查询的结果如下:
这种显然不满足要求,针对这种情况,要怎么做呢?
2、问题解决
这时候需要使用 GROUP_CONCAT() 函数解决此问题。
说明:
复制代码
1.GROUP_CONCAT() 中的值为你要合并的数据的字段名;
SEPARATOR 函数是用来分隔这些要合并的数据的,默认以 逗号 分隔;
’ '中是你要用哪个符号来分隔;
2.必须要用GROUP BY 语句来进行分组管理,不然所有的数据都会被合并成一条记录
复制代码
则此处对应的SQL语句如下,仅供参考!
复制代码
SELECT
su.emp_id,
su.emp_name,
a.notFinished,
b.finished
FROM
(
SELECT
t.emp_id,
t.emp_name
FROM
time_summary t
WHERE
t.time_date >= ‘-06-01’
AND t.time_date <= ‘-06-30’
GROUP BY
t.emp_id,
t.emp_name
) su
LEFT JOIN (
SELECT
t.emp_id,
t.emp_name,
GROUP_CONCAT( SUBSTRING( t.time_date, 9, 2 ) SEPARATOR ‘,’ ) AS notFinished
FROM
time_summary t
WHERE
t.time_date >= ‘-06-01’
AND t.time_date <= ‘-06-30’
AND t.finish_flag = ‘0’
GROUP BY
t.emp_id,
t.emp_name
) a ON su.emp_id = a.emp_id
LEFT JOIN (
SELECT
t.emp_id,
t.emp_name,
GROUP_CONCAT( SUBSTRING( t.time_date, 9, 2 ) SEPARATOR ‘,’ ) AS finished
FROM
time_summary t
WHERE
t.time_date >= ‘-06-01’
AND t.time_date <= ‘-06-30’
AND t.finish_flag = ‘1’
GROUP BY
t.emp_id,
t.emp_name
) b ON su.emp_id = b.emp_id
复制代码
3、内容扩展
在MySQL中字符串拼接常用的方法整理:
1、CONCAT()
2、CONCAT_WS()
3、GROUP_CONCAT()
3.1 CONCAT()
语法:CONCAT(str1,str2,…)
说明:作为最常用的字符串拼接方法,但是CONCAT函数在遇到拼接中的字符串出现 NULL 的情况,会返回 NULL
示例:
3.2 CONCAT_WS()
此种连接字符串的方法,从名字上就能看出:从concat with separator,使用分隔符连接字符串。
语法:CONCAT_WS(separator,str1,str2,…)
使用说明:第一个参数separator是用来分隔其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数
对比说明:concat_ws与concat相比优点:
1、可以使用分隔符连接字符串
2、若连接的字符串出现 NULL 值,concat_ws会忽略 NULL 值,
返回其他连接字符串
示例1:
示例2:
3.3 GROUP_CONCAT()
此种连接方法,主要是将某一字段的值连接成一行进行显示,具体可以参看上面的问题实例。
语法 :group_concat( [DISTINCT] 连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
说明:可以连接多个字段,也可以对连接字段进行排序,默认以 逗号 分隔字段