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

2022 年 7 月 16 日
本文字数：1719 字
阅读完需：约 6 分钟

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

业务场景

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

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

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

业务中用到的技能

左连接获取到其余表中的数据
case 将状态数据转化为文本描述，类似于 1 -> 启用， 0 -> 禁用
表多层嵌套查询
表数据分组查询 + group_concat 连接目标字段合并到 1 条记录中
substring_index，根据分隔符截取字符串
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 spDesFROM        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_codeWHERE        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 '  ' ) spDesFROM        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_ddsubstring_index(str, '_', 1) = aasubstring_index(str, '_', 2) = aa_bbsubstring_index(str, '_', 3) = aa_bb_cc

复制代码

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/79d37d9d4c4909298a885cd94】。文章转载请联系作者。

安逸的咸鱼

关注

还未添加个人签名 2022.07.13 加入

还未添加个人简介

发布

暂无评论

创作场景

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

业务场景

业务中用到的技能

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

联表查询准备基本数据

临时表以 code 分组合并数据

拆分数据到多列

SUBSTRING_INDEX 功能

安逸的咸鱼

评论