相关文章推荐
慈祥的皮带  ·  WPF ...·  1 年前    · 
聪明伶俐的芒果  ·  数据库 - 知乎·  1 年前    · 
跑龙套的筷子  ·  HBase——LSM-Tree - 简书·  1 年前    · 
慷慨大方的泡面  ·  Talk | ...·  1 年前    · 
低调的风衣  ·  2.3.4 ImageView(图像视图) ...·  1 年前    · 
Code  ›  MySQL中将多行查询结果合并为一行展示SQL语句书写 - 星空流年
date 博客园 mysql日期函数 分隔符
https://www.cnblogs.com/cndarren/p/13208124.html
谈吐大方的弓箭
1 年前

最近开发过程中，遇到一个需求是要将所查询的多条结果汇总成一条结果展示，由于之前没有接触过这方面的业务，所以经过一番折腾之后，解决了需求，这里特此记录一下，以供后续参考！

1、问题复现

这里以一个例子进行说明：

需求：一个员工每月是否完成了打卡，要求统计员工当月完成和未完成日期，展示结果如下：

测试的数据库表字段如下： 

CREATE TABLE `time_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;

测试数据如下： 

INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (1, '100', '张三', '2020-06-24', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (2, '100', '张三', '2020-06-23', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (3, '100', '张三', '2020-06-22', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (4, '100', '张三', '2020-06-19', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (5, '100', '张三', '2020-06-18', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (6, '100', '张三', '2020-06-17', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (7, '100', '张三', '2020-06-16', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (8, '100', '张三', '2020-06-15', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (9, '100', '张三', '2020-06-12', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (10, '100', '张三', '2020-06-11', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (11, '100', '张三', '2020-06-10', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (12, '100', '张三', '2020-06-09', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (13, '100', '张三', '2020-06-08', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (14, '100', '张三', '2020-06-05', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (15, '100', '张三', '2020-06-04', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (16, '100', '张三', '2020-06-03', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (17, '100', '张三', '2020-06-02', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (18, '100', '张三', '2020-06-01', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (19, '101', '李四', '2020-06-24', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (20, '101', '李四', '2020-06-23', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (21, '101', '李四', '2020-06-22', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (22, '101', '李四', '2020-06-19', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (23, '101', '李四', '2020-06-18', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (24, '101', '李四', '2020-06-17', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (25, '101', '李四', '2020-06-16', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (26, '101', '李四', '2020-06-15', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (27, '101', '李四', '2020-06-12', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (28, '101', '李四', '2020-06-11', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (29, '101', '李四', '2020-06-10', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (30, '101', '李四', '2020-06-09', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (31, '101', '李四', '2020-06-08', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (32, '101', '李四', '2020-06-05', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (33, '101', '李四', '2020-06-04', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (34, '101', '李四', '2020-06-03', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (35, '101', '李四', '2020-06-02', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (36, '101', '李四', '2020-06-01', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (37, '102', '王五', '2020-06-24', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (38, '102', '王五', '2020-06-23', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (39, '102', '王五', '2020-06-22', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (40, '102', '王五', '2020-06-19', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (41, '102', '王五', '2020-06-18', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (42, '102', '王五', '2020-06-17', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (43, '102', '王五', '2020-06-16', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (44, '102', '王五', '2020-06-15', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (45, '102', '王五', '2020-06-12', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (46, '102', '王五', '2020-06-11', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (47, '102', '王五', '2020-06-10', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (48, '102', '王五', '2020-06-09', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (49, '102', '王五', '2020-06-08', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (50, '102', '王五', '2020-06-05', '1');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (51, '102', '王五', '2020-06-04', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (52, '102', '王五', '2020-06-03', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (53, '102', '王五', '2020-06-02', '0');
INSERT INTO `time_summary`(`id`, `emp_id`, `emp_name`, `time_date`, `finish_flag`) VALUES (54, '102', '王五', '2020-06-01', '0');
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 >= '2020-06-01' and time_date <= '2020-06-30'

这样查询的结果如下：

这种显然不满足要求，针对这种情况，要怎么做呢？

2、问题解决

这时候需要使用 GROUP_CONCAT() 函数解决此问题。 

1.GROUP_CONCAT() 中的值为你要合并的数据的字段名;
　SEPARATOR 函数是用来分隔这些要合并的数据的，默认以 逗号 分隔；
　' '中是你要用哪个符号来分隔；
2.必须要用GROUP BY 语句来进行分组管理，不然所有的数据都会被合并成一条记录

则此处对应的SQL语句如下，仅供参考！ 

SELECT
    su.emp_id,
    su.emp_name,
    a.notFinished,
    b.finished 
    SELECT
        t.emp_id,
        t.emp_name 
        time_summary t 
    WHERE
        t.time_date >= '2020-06-01' 
        AND t.time_date <= '2020-06-30' 
    GROUP BY
        t.emp_id,
        t.emp_name 
    LEFT JOIN (
    SELECT
        t.emp_id,
        t.emp_name,
        GROUP_CONCAT( SUBSTRING( t.time_date, 9, 2 ) SEPARATOR ',' ) AS notFinished 
        time_summary t 
    WHERE
        t.time_date >= '2020-06-01' 
        AND t.time_date <= '2020-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 
        time_summary t 
    WHERE
        t.time_date >= '2020-06-01' 
        AND t.time_date <= '2020-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 值，
            返回其他连接字符串

3.3 GROUP_CONCAT()

此种连接方法，主要是将某一字段的值连接成一行进行显示，具体可以参看上面的问题实例。 

语法 ：group_concat( [DISTINCT] 连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
说明：可以连接多个字段，也可以对连接字段进行排序，默认以 逗号 分隔字段
**************************************************** 林深时见鹿，海蓝时见鲸 ****************************************************
 
推荐文章
慈祥的皮带  ·  WPF 遍历DataTemplate(获取所有控件)_51CTO博客_pywinauto获取控件
1 年前
聪明伶俐的芒果  ·  数据库 - 知乎
1 年前
跑龙套的筷子  ·  HBase——LSM-Tree - 简书
1 年前
慷慨大方的泡面  ·  Talk | 阿里达摩院多语言NLP算法团队程丽颖: 文本生成与论辩挖掘相关的任务与数据集探索 - 知乎
1 年前
低调的风衣  ·  2.3.4 ImageView(图像视图) | 菜鸟教程
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号