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

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

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

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

业务场景

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

image.png

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

这种转化的操作,我们可以使用 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 
复制代码

临时表的效果如下:

image.png

临时表以 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 显示为单条记录,效果如下:

image.png

拆分数据到多列

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

截取后的示意草图:

image.png

这里就用到了 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 执行后的效果如下:

image.png

完美解决问题

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:安逸的咸鱼

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改