【MySql项目实战优化】多行数据转化为同一行多列显示

「这是我参与2022首次更文挑战的第 2 天，活动详情查看：2022首次更文挑战」

在我的工作中，接到过这么一个需求，需要合并纵向的数据为 1 条记录显示出来。

业务场景

用业务上的说法，我这里有 1 个物料，其中有 n 个属性，每个属性配置有是否显示的开关，对于这个物料的 n 个属性，就是 n 条记录存储在这个表中；现在就需要对这个表按物料编码分组，分组后的数据，提取出物料的属性和配置，作为单条记录显示，画一个大概的示意图

这需求的理由是大量的数据拉出来后，上方的模式阅读数据会非常的不直观

这种转化的操作，我们可以使用 java 来处理，拉取到全部的数据后，再根据物料编码进行分组后合并数据即可；但，这次我选择在 mysql 中处理好，直接拿到需要的数据

业务中用到的技能

1. 左连接获取到其余表中的数据
2. case 将状态数据转化为文本描述，类似于 1 -> 启用， 0 -> 禁用
3. 表多层嵌套查询
4. 表数据分组查询 + group_concat 连接目标字段合并到 1 条记录中
5. substring_index，根据分隔符截取字符串
6. replace 替换字符串数据

从内往外开始查询处理为临时表

联表查询准备基本数据

在这一步，获取到全部的基础数据，同时将标记转化位文本描述，将操作后获得的临时表标记为 b

SELECT
        c.mdm_code cCode,
        c.mdm_name cName,
        a.mdm_name aName,
        ( CASE a.mdm_splicing_type WHEN 'B_01' THEN '仅物料描述显示' WHEN 'B_02' THEN '物料描述和物料简称都显示' ELSE '物料描述和物料简称都不显示' END ) aType,
        a.mdm_required aRequired ,
        ap.mdm_des apDes,
        np.mdm_des npDes,
        sp.mdm_des spDes
FROM
        attribute_1 a
        LEFT JOIN mdm_classification c ON a.mdm_classifi_note_id = c.id 
        LEFT JOIN mdm_abbr_spl ap on ap.mdm_product_code2 = left(c.mdm_code,4)
        LEFT JOIN mdm_note_spl np on np.mdm_product_code2 = left(c.mdm_code,4)
        LEFT JOIN mdm_spemod_spl sp on sp.mdm_product_code3 = c.mdm_code
WHERE
        c.mdm_code IS NOT NULL 
) b 

临时表的效果如下：

临时表以 code 分组合并数据

在临时表 b 的基础上，根据 code 分组将后面几列的数据通过 group_concat 合并到 1 个字段上

这次操作获得的临时表标记为 c

SELECT
        cCode,
        cName,
        CONCAT('_',GROUP_CONCAT( aName SEPARATOR ' ' )) aNames,
        CONCAT('_',GROUP_CONCAT( aType SEPARATOR ' ' )) aTypes,
        GROUP_CONCAT( apDes SEPARATOR '  ' ) apDes,
        GROUP_CONCAT( npDes SEPARATOR '  ' ) npDes,
        GROUP_CONCAT( spDes SEPARATOR '  ' ) spDes
FROM
        temp_b

现在所有的数据已经可以按照 code 显示为单条记录，效果如下：

拆分数据到多列

在临时表 c 的基础上，我们对目标字段进行截取，每一列都是截取自目标字段中的一部分内容

截取后的示意草图：

这里就用到了 REPLACE + SUBSTRING_INDEX 组合处理得到切割后对应下标的数据

SELECT
	cCode '三级分类编码',
	cName '三级分类名称',
	REPLACE(SUBSTRING_INDEX(aNames,' ',1),'_','') '属性1',
	REPLACE(SUBSTRING_INDEX(aTypes,' ',1),'_','') '限制1',
	REPLACE(SUBSTRING_INDEX(aNames,' ',2),SUBSTRING_INDEX(aNames,' ',1),'') '属性2',
	REPLACE(SUBSTRING_INDEX(aTypes,' ',2),SUBSTRING_INDEX(aTypes,' ',1),'') '限制2',
	REPLACE(SUBSTRING_INDEX(aNames,' ',3),SUBSTRING_INDEX(aNames,' ',2),'') '属性3',
	REPLACE(SUBSTRING_INDEX(aTypes,' ',3),SUBSTRING_INDEX(aTypes,' ',2),'') '限制3'
FROM
        temp_c

sql 执行后的效果如下：

完美解决问题

SUBSTRING_INDEX 功能

在这个问题的处理过程中，用到了一个核心的方法 SUBSTRING_INDEX(str, delim, count)，功能为字符串截取

但又不同于常规的理解，传入的下标 count 并不是选取 str 截取后对应的下标字符；可以这么理解，count 指从左往右匹配指定次数的分隔符 delim，最后一次匹配时会记录下那个分隔符对应的字符串下标位置 n，最后结果为 str 的前 n 个字符（不包括末尾的分隔符）

来个形象的例子：

str = aa_bb_cc_dd
substring_index(str, '_', 1) = aa
substring_index(str, '_', 2) = aa_bb
substring_index(str, '_', 3) = aa_bb_cc

---

原创文章，未经允许，禁止转载

create by：安逸的咸鱼